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) Does the field order in the tables have anything to do with this or is it just the field order in the query and field order in the index that matter? RBS -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > So, basically it is best to make one large index (apart from the > primary > integer key?) that includes all fields that could be in a WHERE > clause or a > JOIN or a GROUP BY or a HAVING or an ORDER BY? That depends on the queries you want to speed up. Index columns can only be used from left to right, with no skips, to satisfy the conditions of the query. For example, if you have an index on columns (a, b, c) and a query like select * from t where a='x' and c='z'; then the index can be used to satisfy a='x' condition, but then a linear check of all records having a='x' is performed to satisfy c='z'. If you run such a query often and need it to run fast, you may want another index on (a, c), or perhaps (a, c, b). Igor Tandetnik ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
