Mikey C <[EMAIL PROTECTED]> wrote: > Hi, > > I just wanted to ask for confirmation that my understanding on how the query > optimiser works is correct. > > SQLite only uses one index for each table in a FROM?
SQLite uses *at most* one index for each table in the FROM clause. Some tables in the FROM clause might not have useful indices. > > What if tables are joined? Does an index get used for each joined table? Potentially, if suitable indices are available. > > > So if I have > > SELECT * > FROM A > INNER JOIN B > ON A.COL1 = B.COL1 > INNER JOIN C > ON C.COL1 = B.COL2 > > The query optimiser can use 3 indexes max? One on A, B and C Because there is no WHERE clause in this query, one of the tables will not have any constraints available that it can use with an index, so only two indices will end up being used, at most. All else being equal (which rarely is, but just suppose) the indices used will be B(COL1) and C(COL1). > > Since B is joined on COL1 and COL2, only one join can use an index? No. Both joins can use indices, if appropriate indices are available. > > If a WHERE is added: > > SELECT * > FROM A > INNER JOIN B > ON A.COL1 = B.COL1 > INNER JOIN C > ON C.COL1 = B.COL2 > WHERE A.COL2 = 'fred' > > Then again only one index can be used on table A? Either the join or the > where? No. There indices can be used here, assuming appropriate indices exist. Only possible scenario here is to use index A(COL2) to satisfy the WHERE clause, B(COL1) on the first join, and C(COL1) for the second join. -- D. Richard Hipp <[EMAIL PROTECTED]>
