Re: [sqlite] mysql -> sqlite problem
> On Dec 19, 2019, at 4:49 PM, Mike Bentley wrote: > > Is it true that "Each index name must be unique in the database"? Yes. You're right that the docs don't seem to spell this out, but since the syntax to delete an index is "DROP INDEX ", you couldn't have multiple indexes with the same name because it wouldn't know which one to delete. You can pretty easily get a unique index name by prefixing the name of the table. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MySQL / SQLite
Puneet Kishor wrote: 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. Clearly I do not know enough about PHP and Perl syntax to tell the difference between the two. :-) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] MySQL / SQLite
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 1, {
Sqlite => \,
MySQL => \,
};
__END__
I got the following result --
D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 1 iterations of MySQL, Sqlite...
MySQL: 1409 wallclock secs (396.52 usr + 649.03 sys = 1045.55 CPU)
@ 9.56/
s (n=1)
Sqlite: 2 wallclock secs ( 1.22 usr + 0.80 sys = 2.02 CPU) @
4960.32/s (n
=1)
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]
Re: [sqlite] MySQL / SQLite
rich coco wrote: i am curious as to the discrepancies in 'sys' time between sQLite and mySQL (9.12s .vs 1.96s as reported below). SQLite lacks a persistent server, so it has to flush its cache and reread every page of the database for each of the 100 queries. This takes time. MySQL, on the other hand gets to keep all those pages in memory. If I change the query slightly so that it begins like this: PRAGMA cache_size=2; BEGIN; That allows SQLite to hold all database pages in cache after the initial read. In that case, SQLite is much faster: [EMAIL PROTECTED] bld]$ time ./sqlite test.db /dev/null real0m13.403s user0m13.190s sys 0m0.080s also, the 'usr + sys' time for SQLite more or less equals the real time. but for mySQL, the 'real' time is over 5s longer than the respective 'usr + sys' times (25% greater). With SQLite, all processing happens in a single process so you see it all with the "time" command. With MySQL, you are only seeing the time used by the client-side. The server-side processing is omitted from the "user" and "sys" times. But it is included in the real time, of course. I took your data and loaded it into SQLite and MySQL databases. Then I create a script file that contains 100 instances of your query. Here is what I get: [EMAIL PROTECTED] bld]# time mysql drh /dev/null real0m25.585s user0m18.290s sys 0m1.960s [EMAIL PROTECTED] bld]# time ./sqlite test.db /dev/null real0m22.993s user0m13.870s sys 0m9.120s 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. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] MySQL / SQLite
I totally agree...
Seems like when users say SQLite is slower than "xyz...", they are using
a high level driver based interface instead of using a "c" based driver
program to
really test what SQLite is doing.
I have written tests at the "c" level for both MySQL and SQLite
and SQLite is generally much faster. The MySQL overhead is probably due to
tcp/ip communication...
Now where SQLite seems to slow down is when the database contains over a
gigabyte of data.
Hopefully SQLite ver 3.xx will fix that...
-Original Message-
From: Jimmy Lantz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 1:39 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] MySQL / SQLite
At 19:32 2004-04-14, you wrote:
>>Yes, but are mysql_query and sqlite_query really doing the same thing? It
>>is quite possible that mysql_query doesn't actually perform the complete
>>query, while sqlite_query does. I think a better test would be to do the
>>query and then step through all of the results, doing something with each
>>row. What is the time difference for that?
>
>Ok:
>
>$array = array();
>include("../cardsearch/setMySQL.php");
>$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
>while($row=mysql_fetch_row($erg)) $array[] = $row;
>
>$array = array();
>$db = sqlite_open("speedtest.sqlite");
>$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE
'%a%'");
>while($row=sqlite_fetch_array($result)) $array[] = $row;
>
>MySQL: 0.24748015403748
>SQLite: 0.68342804908752
>
>$array = array();
>$db = sqlite_open("speedtest.sqlite");
>$result = sqlite_unbuffered_query($db, "SELECT * FROM speedtest WHERE
>text5 LIKE '%a%'");
>while($row=sqlite_fetch_array($result)) $array[] = $row;
>
>MySQL: 0.23681807518005
>SQLite: 0.64980888366699
>
>I ran it several times, of course.
I think there might be some work needed on the PHP implementation of SQLite
(marked as experimental)
Whereas the MySQL implementation has had years to mature and improve.
Do your tests using the CLI instead and see if you get the same.
/ Jimmy
>-hannes
>
>
>
>
>-
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately.
Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.
Re: [sqlite] MySQL / SQLite
At 19:32 2004-04-14, you wrote:
Yes, but are mysql_query and sqlite_query really doing the same thing? It
is quite possible that mysql_query doesn't actually perform the complete
query, while sqlite_query does. I think a better test would be to do the
query and then step through all of the results, doing something with each
row. What is the time difference for that?
Ok:
$array = array();
include("../cardsearch/setMySQL.php");
$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
while($row=mysql_fetch_row($erg)) $array[] = $row;
$array = array();
$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
while($row=sqlite_fetch_array($result)) $array[] = $row;
MySQL: 0.24748015403748
SQLite: 0.68342804908752
$array = array();
$db = sqlite_open("speedtest.sqlite");
$result = sqlite_unbuffered_query($db, "SELECT * FROM speedtest WHERE
text5 LIKE '%a%'");
while($row=sqlite_fetch_array($result)) $array[] = $row;
MySQL: 0.23681807518005
SQLite: 0.64980888366699
I ran it several times, of course.
I think there might be some work needed on the PHP implementation of SQLite
(marked as experimental)
Whereas the MySQL implementation has had years to mature and improve.
Do your tests using the CLI instead and see if you get the same.
/ Jimmy
-hannes
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] MySQL / SQLite
Hannes Roth wrote:
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
I took your data and loaded it into SQLite and MySQL databases.
Then I create a script file that contains 100 instances of your
query. Here is what I get:
[EMAIL PROTECTED] bld]# time mysql drh /dev/null
real0m25.585s
user0m18.290s
sys 0m1.960s
[EMAIL PROTECTED] bld]# time ./sqlite test.db /dev/null
real0m22.993s
user0m13.870s
sys 0m9.120s
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.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] MySQL / SQLite
Hannes Roth wrote:
$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
Yes, but are mysql_query and sqlite_query really doing the same thing? It is quite possible that mysql_query doesn't actually perform the complete query, while sqlite_query does. I think a better test would be to do the query and then step through all of the results, doing something with each row. What is the time difference for that?
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] MySQL / SQLite
Hannes Roth wrote: Ok: -18 fields, all strings or numbers. -No indices. -The query I used is "SELECT * FROM table WHERE field1 LIKE '%foo%'. This is similar to Test-5 at http://www.sqlite.org/speed.html In Test-5, SQLite is 30% faster than MySQL. I do not know what the difference might be from what you are seeing. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] MySQL / SQLite
Dear D. Richard Hipp, I had some strange result on some PCs, a) On my notebook (P3 899, 256Mbytes, WinXP) when do insert a records to a blank table, it > 100% slower as compare to other. (< 3000 rows). b) On one of PC (Celeron 1.7G, 256MBytes WinXP), when do search. it will 100% slower as compare to others, (> 8000 rows) Infact it slower then a Celeron 633 with 128M Win98. for indx := 0 to QstLst.Count - 1 do begin qryQst.SQL = 'Select * from QstTbl As Q, LstTbl As L where L.QstID=''' + QstLst.Strings[indx] + ''' AND Q.QstID=L.QstID'; qryQst.Open( ); ... ... end; Do u have any complete compiled application to check the perfomance. Regards KL Chin -Original Message- From: D. Richard Hipp [SMTP:[EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 7:15 PM Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] MySQL / SQLite Hannes Roth wrote: > 2. Why is SQLite twice as fast when using a small database (<3000 rows) > and twice as slow when using a large database (>8000 rows)? > The speed comparison at http://www.sqlite.org/speed.html uses tables with 25000 rows and is twice as fast as MySQL. I don't know why yours is slower - you did not give much data to go on. Perhaps if you supplied some information about your database (the schema, what indices are defined, what data is being stored, what your queries look like) someone could better answer your question. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] MySQL / SQLite
Hannes Roth wrote: 2. Why is SQLite twice as fast when using a small database (<3000 rows) and twice as slow when using a large database (>8000 rows)? The speed comparison at http://www.sqlite.org/speed.html uses tables with 25000 rows and is twice as fast as MySQL. I don't know why yours is slower - you did not give much data to go on. Perhaps if you supplied some information about your database (the schema, what indices are defined, what data is being stored, what your queries look like) someone could better answer your question. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] MySQL / SQLite
Hannes Roth schrieb: 1. Why is the SQLite database much much bigger than the MySQL table and the raw csv file? I didn't use any index and vacuumed all the time ;) SQLite does store everything as string, therefore the DB cannot be significantly small than the CVS-file. MySQL does store integers as binary values, this does require less space. 2. Why is SQLite twice as fast when using a small database (<3000 rows) and twice as slow when using a large database (>8000 rows)? Is this a harddrive issue? I would assume that SQLite does have less overhead on each query. SQLite runs directly embedded into application process, MySQL is an Server - there is additional cost for TCP/IP communication. Such things might easyly result in beeing faster on small jobs and slower on large ones - the communication overhead is mostly independent of the number of records in the table (as long as this does not enlagre the result-set) Elmar - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
