Re: [sqlite] sqlite too slow for me?

[Péter Szabó]

First, thank you all for the answers.


   UNIQUE(col1, col4, col5, col2),


Adding this would surely make the query run faster -- provided that
SQLite chooses the right index. But I also use the UNIQUE(col1, col4,
col5) constraint to ensure the uniqueness of these three columns. So
instead I should have both

 UNIQUE(col1, col4, col5),
 UNIQUE(col1, col4, col5, col2),

but this would waste my disk space.

I am wondering how can MySQL 4.1 be so fast compared to SQLite 3?
MySQL answers my query in 0.02 seconds, and SQLite answers in more
than 28 seconds.

I guess that MySQL doesn't do any magic either (i.e. it operates in
O(Klog N) time, which appears to be just 0.02 seconds), but MySQL
recognises that it should use the UNIQUE(col1, col4, col5) index,
while SQLite poorly chooses some other index, maybe the PRIMARY
KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone
confirm that the SQLite is using the wrong index? Is it possible to
deterministically rewrite the query to force SQLite use the right
index?

[sqlite] sqlite too slow for me?

[Péter Szabó]

Dear SQLite Developers,

I am seeking help for optimizing my SQLite SQL query, which seems to
be running unreasonably slow. The query is:

 SELECT col2 FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';

My schema is the following:

 CREATE TABLE t (
   col1 text NOT NULL,
   col2 integer NOT NULL,
   col3 integer NOT NULL,
   col4 text NOT NULL,
   col5 text NOT NULL,
   PRIMARY KEY(col1, col2, col3, col4, col5),
   UNIQUE(col1, col4, col5),
   UNIQUE(col4, col5, col1),
   UNIQUE(col5, col4, col1),
 );

Some statistics:

 SELECT COUNT(*) FROM t;
 355113

 SELECT COUNT(*) FROM t WHERE col1='foobar';
 355113

 SELECT COUNT(*) FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';
 96

 SELECT COUNT(DISTINCT col5) FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';
 96

All the above operations returning 96 are unmeasurably fast, i.e. they
return their answer immediately.

This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col5):

 SELECT col5 FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';

However, this is very slow in SQLite 3.3.5:

 SELECT col2 FROM t WHERE col1='foobar' AND
   col4='foobarfoobarfoobarfoob';

It takes 25 seconds to return all the 96 rows on my PC (Celeron 2400
MHz, 512 MB of RAM, Linux). This is way too much for me. I was
expecting an instant answer.

I've also run these queries on the same data, using a MySQL server
version 4.1.4 with InnoDB tables, and all them, including the `SELECT
col2 ...' were unmeasurably
fast! (This is not because MySQL caches queries or results -- I've
restarted the MySQL server between each query.)

Is it possible to speed up the `SELECT col2 ...' query in SQLite? What
should I do?

Thanks,

Péter Szabó
free software consultant
Free Software Institute, Hungary