>You need to sort the output of EXPLAIN QUERY PLAN. Apologies for not spoting the need to sort the query plan! A fundamental error on my part.
However, in real world application 1) is significantly slower than 2) despite returing the same number of rows. If I correctly read the plans this time, it is because 2) searches the song_artist table using an index but 1) scans the entire table. Is there anything I can do to get the optimiser to perform 1) with the same efficiency as 2)? In real world application I use a "songartistview" as a means to list and alias name the fields wanted from joins over several tables. This was fine until I needed to use it in a left join, and the speed plumited. For readablity and management I would like to continute to use a view, but it is so slow. Is there any alternate to having to use tables like 2) does? On 14 February 2016 at 15:15, Clemens Ladisch <clemens at ladisch.de> wrote: > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
