Thanks; I think I now know most rules to create indexes in a sensible way. One thing I am not sure about yet is when an index would be helpful in the first place in relation to the data in the field. I understand an index is going to help little if the values in a particular field can only for example be 1 or 0, but roughly when does it become useful to add an index? This is when the cost (time) of adding the index doesn't matter. I don't have to worry about inserts in this case.
Maybe this whole topic should be covered somewhere in the documentation. RBS -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 16:14 To: SQLite Subject: [sqlite] Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > How does the field order in indexes work with joins? > So for example given the query: > > select > t1.a, > t1.b, > t2.c > from > table1 t1 inner join table2 t2 on > (t1.id1 = t2.id2) > where > t1.a = 'abc' > > would the index need to be > (a, id1) > or > (id1, a) Doesn't matter. SQLite internally converts the original query to something like select t1.a, t1.b, t2.c from table1 t1, table2 t2 where t1.id1 = t2.id2 and t1.a = 'abc'; It then knows that the two operands of the AND can be checked in any order, so it could use either index. If, on the other hand, the last condition were t1.a >= 'abc', then an index on (id1, a) could be used to satisfy both conditions, but an index on (a, id1) only works for inequality but doesn't help with t1.id1=t2.id2 > Does the field order in the tables have anything to do with this No. > or is it just the field order in the query The field order in the query doesn't matter much, either. SQLite is smart enough to rearrange the checks in a variety of ways. Igor Tandetnik ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
