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
