I sense the beginning of a "How to use indexes" page. Things you need know: Only one index is used per query (or insert??). So multiple indexes will not do you any good for a single select (you might still need them for each different WHERE or ORDER you do though). Recommendations Index order is important, "SELECT * FROM table WHERE a='a' ORDER BY b"; should have "CREATE INDEX i ON TABLE(a,b)" and not (b,a). If you also reversed the WHERE and ORDER clause (doing both selects) you would want both indexes. Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov Sent: Thu 6/17/2010 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT behaviour with INDEX > Is there any advantage/disadvantage to having seperate indexes in a case like > this? SQLite uses at maximum one index per table per query. So there is a big disadvantage in having separate indexes especially if @IdClient is not selective enough. Pavel On Thu, Jun 17, 2010 at 9:44 AM, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > Is there any advantage/disadvantage to having seperate indexes in a case like > this? > > In other words, as a simple design goal to have a separate index that matches > whatever you select on and order on? > > It seems to me that if you do "SELECT * FROM log WHERE id_clie...@idclient > ORDER BY utc DESC LIMIT 1" aren't you better off with two indexes, one for > id_client and one for utc? That way you don't have to worry about order > dependence at all. It seems only if you start doing WHERE clauses on both > should you need a compound index. > > And in this specific case unless you have a boat load of utc's for each > client_id the utc index isn't going to buy you much at all. > > Michael D. Black > Senior Scientist > Northrop Grumman Mission Systems > > > ________________________________ > > From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov > Sent: Thu 6/17/2010 7:40 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SELECT behaviour with INDEX > > > >> It seems to me the DB engine tries to perform an unindexed search if the >> requested value is not present in the index. >> Is this behaviour intended ? How can I get rid of it ? > > If your index is in that very order as you said, i.e. (utc, > id_client), then whatever @IdClient you put in the query SQLite will > perform full index scan because it can benefit from index only for the > purpose of ordering but not for the purpose of searching. You've just > got lucky having LIMIT 1 in the query and providing @IdClient that has > some utc close to maximum. If you provide @IdClient that has utc close > to minimum then you'll get long execution time again. But if you > change your index to (id_client, utc) then you'll have fast times for > any @IdClient. > > > Pavel > > On Thu, Jun 17, 2010 at 8:32 AM, Gregoire de Turckheim > <gdt--sql...@taveo.com> wrote: >> Hi, >> >> I'm noticing a strange behaviour with SELECT statements. >> >> Let's consider this statement : >> >> SELECT * FROM log >> WHERE id_clie...@idclient >> ORDER BY utc DESC >> LIMIT 1 >> >> The table has an index on the "utc" and "id_client" fields pair. >> >> When running this query with an @IdClient value which exists in the >> table, the query returns instantly (< 200ms) >> When running this query with an @IdClient value which doesn't exist in >> the table, the query is slow (2,63s) >> When running this query with an @IdClient value which exists in the >> table and the "NOT INDEXED" keywords, the query is slow too (2,84s) >> >> It seems to me the DB engine tries to perform an unindexed search if the >> requested value is not present in the index. >> Is this behaviour intended ? How can I get rid of it ? >> >> Regards, >> >> -- >> Grégoire de Turckheim - TAVEO >> Gérant - www.taveo.com >> Mob: 06 26 92 40 02 - Tel: 09 50 58 40 02 >> >> _______________________________________________ >> 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 > > > > _______________________________________________ > 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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users