Is MySQL much faster then SQLite 3? 1

[sen5241b]

I created a a database table in MySQL called placenames -a simple list of 260K place names. The following code finds a name in the table super fast.

$result = mysql_query("SELECT * FROM placenames");
while($row = mysql_fetch_array($result))
{ if ($place == $row['placename'])  { do whatever etc ..

I created the same data in a SQLite 3 database with an index and it takes forever to read through the table and it hangs the server before it can finish executing this code:

foreach ($dbh->query('SELECT * FROM placenames') as $row) 
{ if ($place == FoldDiacritics($row['placename']) ) {  do whatever etc ..

Seems like there is no random access with the SQLite database even with the index. Why is MySQL so much faster?

 

[feherke]

Hi

Let us start with some tests :

[blue]master #[/blue] time mysql test260k <<< 'select count(*) from test260k'
count(*)
260000
time 0m0.012s

[blue]master #[/blue] time mysql test260k <<< 'select * from test260k' > /dev/null
time 0m0.515s

[blue]master #[/blue] time mysql test260k <<< 'select * from test260k order by name' > /dev/null
time 0m0.515s

[blue]master #[/blue] time sqlite3 test260.sqlite <<< 'select count(*) from test260k;'
260000
time 0m0.014s

[blue]master #[/blue] time sqlite3 test260.sqlite <<< 'select * from test260k;' > /dev/null
time 0m0.131s

[blue]master #[/blue] time sqlite3 test260.sqlite <<< 'select * from test260k order by name;' > /dev/null
time 0m0.136s

My conclusion : if your SQLite query is so slow, the difference is probably elsewhere, for example :
[ul]
[li]handled by different database abstraction layers in PHP[/li]
[li]stored on different filesystems[/li]
[/ul]


Feherke.

 

[sen5241b]

Seems the 'where' clause provides random access.

 

[feherke]

Hi

Their performance is still quite constant here :

[blue]master #[/blue] time mysql test260k <<< "select count(*) from test260k where name='test2011'"
count(*)
1
time 0m0.012s

[blue]master #[/blue] time sqlite3 test260k.sqlite <<< "select count(*) from test260k where name='test2011';"
1
time 0m0.003s

Feherke.

 

[sen5241b]

Seems like sqlite might even be faster.