>1) has "song LEFT JOIN (song_artist JOIN artist)". >3) has "(song LEFT JOIN song_artist) JOIN artist". >2) has "(song LEFT JOIN song_artist) LEFT JOIN artist". >4) has "song LEFT JOIN (song_artist LEFT JOIN artist)".
OK, I see how you are saying the queries differ. 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. You rightly said > A left join forces SQLite to scan the outer table first. This is > necessary for the join to work correctly. But it is not scanning song, the outer table, first. It is not optimising correctly. Is there a way with 1) to get it to scan song first? Can you see why that would be the optimal plan? On 14 February 2016 at 10:49, Clemens Ladisch <clemens at ladisch.de> wrote: > Dave Blake wrote: > > 3) SELECT song.*, song_artist.*, artist.* > > FROM song LEFT JOIN song_artist ON song.idSong = song_artist.idSong > > JOIN artist ON song_artist.idArtist = artist.idArtist > > WHERE song.idSong =1 > > > > This ... has the same join combination as 1). > > No. > 1) has "song LEFT JOIN (song_artist JOIN artist)". > 3) has "(song LEFT JOIN song_artist) JOIN artist". > > > Similarly trying a view using outer join > > > > CREATE VIEW songartistleftview AS SELECT song_artist.idSong AS idSong, > > song_artist.idArtist AS idArtist, > > artist.strArtist AS strArtist > > FROM song_artist LEFT JOIN artist ON song_artist.idArtist = > artist.idArtist > > > > New query > > 4) SELECT song.*, songartistleftview.* FROM song > > LEFT JOIN songartistleftview ON song.idSong = songartistleftview.idSong > > WHERE song.idSong =1 > > > > 4) has same slow query plan as 1) despite having all left joins like 2). > > There are still differences: > 2) has "(song LEFT JOIN song_artist) LEFT JOIN artist". > 4) has "song LEFT JOIN (song_artist LEFT JOIN artist)". > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
