On Tue, Jun 4, 2013 at 10:41 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 5 Jun 2013, at 3:16am, David de Regt <dav...@mylollc.com> wrote: > > > CREATE TABLE test (col1 text, col2 text, col3 text); > > CREATE INDEX tindex ON test (col1, col2, col3); > > > > explain query plan > > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c'; > > > > The above returns: > > SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows) > > Other people have answered, but I don't think anyone has come out and made > this plain: the order of the columns in your index is significant. For > this specific SELECT, a more useful index would be > > CREATE INDEX tindex132 ON test (col1, col3, col2) > > and indeed this would not help any more than > > CREATE INDEX tindex13 ON test (col1, col3) > > Actually, tindex132 would be a little faster than tindex13, since with tindex132 it can look up the value of col2 directly from the index and never has to seek into the original table. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
