Help me : SQLite versus mySQL in PHP

Edited: Thanks to “no one” for comment on transaction mode INSERTION, with BEGIN IMMEDIATE-COMMIT pair that wrap INSERT QUERY. I got double perfomance than MySQL with this (1000 insertion, Mysql took 0.1 second, and sqlite 0.05 second)

Hi guys,

Lately, I read in http://www.sqlite.org/speed.html, that sqlite is a bit faster than mysql and postgreSQL. But, when I perform a little testing to compare them in PHP, I got a contrast result.

For both testing, I use this table :
CREATE TABLE test (a varchar(20) PRIMARY KEY, b varchar(20), c varchar(20), d varchar(20), e varchar(20),  f varchar(20), g varchar(20), h varchar(20),  i varchar(20), j varchar(20))

for that table, I perform 5000 insert for both sqlite and mysql :
here’s for sqlite code :
=============== sqlite-test.php ===============

$time_start = microtime(true);
$db = sqlite_open('pegawai.db', 0666, $sqliteerror);

sqlite_exec($db, "PRAGMA synchronous = OFF;");
for($i = 0; $i < 5000; $i++)
{
$sql = "INSERT INTO test VALUES ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
$q = sqlite_query($db, $sql);
}
sqlite_close($db);
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "executed in $time seconds\n";

=========================================

and here’s for mysql code :
============= mysql-test.php ================

.............
$time_start = microtime(true);
for($i = 0; $i < 5000; $i++)
{
$sql = "INSERT INTO test VALUES ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
$q = mysql_query($sql);
}
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "executed in $time seconds\n";
..............

===========================================

when I run those 2 script, the first one (sqlite) run for 43.4918169975 seconds, while the second (mysql) only take 0.52137298584 seconds.

It means that sqlite run 80x longer than mysql did (very contrast with the one I read in http://www.sqlite.org/speed.html).

Please tell me where do I did wrong. Thanks very much.

Tags: , , , ,

About sisusilo

I was born after we celebrate youngman promise about their commitment to this country

11 responses to “Help me : SQLite versus mySQL in PHP”

  1. pebbie says :

    mmm.. kok curang yah? assignment start time untuk sqlite nggak ditaro pas sebelum for loop.. kalo gitu, start time untuk mysql juga harus ditaro sebelum mysql_open dong..😛

  2. pebbie says :

    tambahan, sqlite_close juga jangan masuk hitungan dong..

  3. rockerbad says :

    stuju sama pebbie…
    gw emang ga tau seberapa ngaruhnya itu open sama close db, tapi knapa g dicoba? sapa tau emang lamanya disitu!?

  4. antonrifco says :

    @all :
    sebenernya di code aslinya, time_start itu aku taro di paling atas code, setelah

    yg aku bingung itu kok, sqlite jatuhnya 80x lebih lambat daripada mysql, dan untuk update (code tidak tercantum), waktunya bahkan sqlite sampai 100x kali lebih lambat daripada mysql.

  5. antonrifco says :

    comment di atas beneran gw (lupa login :P)

    sebenernya gini kasusnya bro.
    ada aplikasi yang aku kembangin butuh untuk log aktivitas untuk setiap transaksi yg ada (fail atau sukses).

    Untuk sekarang, mekanisme log nya masih ke dalam flat file.
    kelebihannya : bisa memanfaatkan fitur tail -f untuk update log.
    kekurangannya : jika ingin mendapatkan report transaksi tertentu pada waktu tertentu agak repot (jika bisa dibilang bahkan hampir tidak mungkin).
    misalnya : jumlah transaksi yg gagal dari user a pada tanggal 17 Maret 2009 – 24 Maret 2009

    repot kan….

    solusi alternatif nya bisa dialihkan dalam sebuah database (log nya), hanya saja cost koneksinya terlalu besar (bayangkan aplikasi ini mempunyai rate transaksi 1000 / detik, repot kan…)

    pas aku baca2 artikel di sqlite, ada wacana yg menuliskan bahwa sqlite beberapa kali lebih cepat daripada mysql dan postgreSQL.

    sempat terpikir untuk mencoba ni database, tapi ketika testing sederhana seperti di atas, kok malah kebalikannya yang terjadi

    huhuhu….

  6. Zakka Fauzan Muhammad says :

    Komen gak nyambung, mengingat saya gak skill😀

    Please tell me where do I did wrong.

    Please tell me where do I did wrong…😛

    Kalo soal kodenya, gak bisa komen, pake sqlite juga cuma sekali seumur hidup😀

  7. antonrifco says :

    ih pinter banget deh zakka
    berapa sih toefl nya ^^

  8. no one says :

    Try wrapping your INSERTS with BEGIN IMMEDIATE and COMMIT and I think you’ll find a dramatic performance improvement.

  9. Penjahat says :

    no one is may be right
    try the following statement
    – OPENDATABASE
    – EXEC
    – BEGIN IMMEDIATE
    – INIT START TIME
    – LOOP insert
    – INIT END TIME
    – COMMIT
    – CLOSE DB

    please tell me if it’s help

  10. avriananda says :

    Dulu gw sempet kepikiran make SQL lite dikombinasikan Sama Microsoft Excel dan ternyata bisa.. Sayang belom banyak kesempatan nyobainnya..

  11. nuri says :

    mysqlnya isam pa innodb ?🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: