Dave Blake wrote: > What I see as wrong is that in 1) (and 4) ) we have a query of the form > A LEFT JOIN B WHERE clause involving index fields on A > > yet the optimiser does not search A, the outer table, first using the index.
EXPLAIN QUERY PLAN SELECT song.*, songartistview.* FROM song LEFT JOIN songartistview ON song.idSong = songartistview.idSong WHERE song.idSong =1; 1|0|0|SCAN TABLE song_artist 1|1|1|SEARCH TABLE artist USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|SEARCH TABLE song USING INTEGER PRIMARY KEY (rowid=?) 0|1|1|SCAN SUBQUERY 1 You need to sort the output of EXPLAIN QUERY PLAN. The song table is searched first. Regards, Clemens
