Okay, but, it's essentially doing the equivalent of a "table scan" over the portion of the index where col1='a', so if col1='a' doesn't actually end up narrowing down the resultset hugely, you're still better off with a properly ordered index, correct? (with YMMV disclaimers)
-David -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, June 4, 2013 7:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Covering Index? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
