On Tue, Jun 4, 2013 at 10:30 PM, David de Regt <dav...@mylollc.com> wrote:
> 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) > All indices in SQLite are ordered. So I'm not sure what you mean by a "properly ordered index"... Yes, you are doing a "table scan" over some slice of the index. But, hopefully the col1='a' constraint limits the size of that slice by some amount, say 1/10th the size of the complete index. So it is helping performance some. > > -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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
