Since col1 is always equal to 'foobar' in your data set, you want to discourage its use by the SQLite optimizer by using +col1 in the where clause. Any operation on a column in the where clause disqualifies it from be used in an index in SQLite.
SELECT col2 FROM t WHERE +col1='foobar' AND col4='foobarfoobarfoobarfoob'; With the above where clause, the unique index (col4, col5, col1) will be used instead of the less efficient PRIMARY KEY(col1, col2, col3, col4, col5). WHERE (col1||"")='foobar' would have also worked, but less efficiently than the unary +. I don't think that the syntax of +col1 is portable to other databases when non-numeric types are involved, but non-trivial SQL is generally unportable on popular databases anyway. --- Péter Szabó <[EMAIL PROTECTED]> wrote: > 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 > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
