>The result is correct. I am so surprized by your assertion I am sure there is some misunderstanding. The way 1) is performed, scan song_artist first, is suboptimal. Try my example with ~8000 rows and a where clause that returns multiple songs and you will see what I mean.
>This would not necessarily be the case if it >executed an outer join as if it were an inner join. Not sure what you are saying here? >A left join forces SQLite to scan the outer table first. This is >necessary for the join to work correctly. Yes. With "A LEFT JOIN B" the outer table is "A", and I expect it to be scanned first. If there is a where clause on index fields from A I expect that index to be searched first. That is how 2) is done, but in 1) it does not. >No, the difference is that the view uses an inner join, while your >second query use only outer joins. OK, look at this variation 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 produces the same (optimum) query plan as 2), and has the same join combination as 1). Of couse if there are no song_artist records for idSong = 1 then this format of query will not return the same results as 1) or 2) 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). I genuinely believe that the optimiser is choosing the wrong plan for 1) and 4). I do not see your argument for correctness. Could you please look at this again. Moreover earlier versions of SQLite e.g. 3.8.3.1 got it right and use same efficient plan for 1), 2), 3) and 4). The current behaviour is making views unusable in my application. On 12 February 2016 at 20:47, Clemens Ladisch <clemens at ladisch.de> wrote: > Dave Blake wrote: > >> It chooses a _correct_ plan. > > > > Really? With query 1) to select a song it first scans the song_artist > table > > that could contain many thousands of records. That does not seem right. > > The result is correct. This would not necessarily be the case if it > executed an outer join as if it were an inner join. > > A left join forces SQLite to scan the outer table first. This is > necessary for the join to work correctly. > > >> Your queries are quite different. > > > > They produce the same result sets, but yes one uses a view and the other > > the constitent tables. > > No, the difference is that the view uses an inner join, while your > second query use only outer joins. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
