In your test case it would be better to have an index on (col1,col3,...) or (col3,col1,...) because SQLite could then scan only the desired rows. I assume that is what you mean by "properly ordered".
Read performance is determined by how well the indexes match the select list, where constraints and order by clause. Write performance is negatively impacted by having too many indexes. -----Ursprüngliche Nachricht----- Von: David de Regt [mailto:dav...@mylollc.com] Gesendet: Mittwoch, 05. Juni 2013 04:30 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Covering Index? 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 -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
