Re: [sqlite] MySQL / SQLite

Wed, 14 Apr 2004 12:19:28 -0700

Hannes Roth wrote:

Hi.

I don't want to publish that table I used to make that benchmark. So I created some random data:
http://dl.magiccards.info/speedtest.tar.bz2

$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE '%a%'");

include("MySQL.php");
$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");

MySQL: 0.13727307319641
SQLite: 0.17734694480896

-hannes



I loaded the above data into MySQL 4.01 on my dual P4, 2.4 GHz, 1 Gb Ram, WinXP box appropriately called "Lazy," and ran the following script using Activestate Perl 5.8.1 (while iTunes was happily streaming KCRW simulcast).

#!perl.exe -w

use strict;
use Benchmark;
use DBI;

my $sql = "SELECT * FROM speedtest WHERE text5 LIKE '%a%'";

my $mh = DBI->connect('dbi:test:mysql', '', '');
my $msth = $mh->prepare(qq{$sql});

sub mysql {
  $msth->execute;
}

my $sh = DBI->connect('dbi:SQLite:speedtest.sqlite', '', '');
my $ssth = $sh->prepare(qq{$sql});

sub sqlite {
  $ssth->execute;
}

timethese 10000, {
  Sqlite => \&sqlite,
  MySQL => \&mysql,
};

__END__

I got the following result --


D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 10000 iterations of MySQL, Sqlite...
MySQL: 1409 wallclock secs (396.52 usr + 649.03 sys = 1045.55 CPU) @ 9.56/
s (n=10000)
Sqlite: 2 wallclock secs ( 1.22 usr + 0.80 sys = 2.02 CPU) @ 4960.32/s (n
=10000)


Then I changed the subroutines to actually fetch the data, so they were now

sub mysql {
  $msth->execute;
  my $mrow = $msth->fetchrow_arrayref;
}

sub sqlite {
  $ssth->execute;
  my $srow = $ssth->fetchrow_arrayref;
}

and ran the test 1000 times. I got --

D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 1000 iterations of MySQL, SQLite...
MySQL: 141 wallclock secs (38.80 usr + 66.20 sys = 105.00 CPU) @ 9.52/s (n
=1000)
SQLite: 0 wallclock secs ( 0.13 usr + 0.11 sys = 0.23 CPU) @ 4273.50/s (n
=1000)
(warning: too few iterations for a reliable count)

Then I changed the SQL statement to actually fetch the count so it was now --

my $sql = "SELECT COUNT(*) AS foo FROM speedtest WHERE text5 LIKE '%a%'";

and surprisingly the situation reversed. SQLite was now slower --

D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 1000 iterations of MySQL, SQLite...
MySQL: 25 wallclock secs ( 0.11 usr + 0.05 sys = 0.16 CPU) @ 6451.61/s (n
=1000)
(warning: too few iterations for a reliable count)
SQLite: 39 wallclock secs (20.70 usr + 17.78 sys = 38.49 CPU) @ 25.98/s (n=1
000)

SQLite also slowed dramatically when using fetchall_arrayref({}). As far as I can see, it is almost impossible to compare because results depend so much on what one is trying to do.

In response to hannes -- no, you don't have to change to PHP mailing list for SQLite questions, however, the PHP mailing list might be more appropriate for questions regarding PHP's SQLite implementation. This is, after all, the SQLite list, and most folks here seem to be C programmers even while they seem to happily assist with non-SQLite questions.

The lesson here is that db performances differ because of numerous reasons. If MySQL is faster than SQLite under the conditions in which you are working, and if speed is important for you, then you should stick with MySQL. If you like SQLite for what it gives (supreme simplicity with darn good speeds), then you should choose the best tools to make it work most optimally.

To Richard --

So in my test, SQLite is a little faster.  Perhaps the difference
might be in a bad implementation of the SQLite bindings for Perl,
or perhaps the "mysql" command-line shell is less than optimal.

You perhaps meant "PHP" instead of "Perl" as that is what hannes is using. As far as I can see, DBD-SQLite is a most excellent product.


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to