On Tue, Jun 4, 2013 at 10:16 PM, David de Regt <dav...@mylollc.com> wrote:
> Quick question, SQLites, > > 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) > > > Which of the following is happening: > 1. It's actually only using it as an "index" for the col1 check, and then > just using the fact that, somewhere, it contains the rest of the data for > the query inside the index, which is, in theory, faster than table scanning > the actual table for the results, but you're getting no search performance > gain out of anything other than the col1 part of the index. > 2. It's actually somehow using it as an optimized index over both col1 and > col3 conditions, but only saying col1 in the explain. > 3. Other..? > It seeks to the first entry of the index where col1='a', then starts reading entries sequentially as long as col1 continues to equal 'a'. Thus, only a small part of the index is examined, and the table itself is never even opened. > > Thanks! > -David > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
