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
