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
