> 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
