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]
-----------------------------------------------------------------------------
