Thank you for looking at this. >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. >Your queries are quite different. They produce the same result sets, but yes one uses a view and the other the constitent tables. >What is your query actually supposed to do? I have simplified things to make the issue clear, obviously unsuccesfuly. In my application the query is more complex. In this example there is a many to many relationship between songs and artists resolved using a song_artist link table. Both 1) and 2) return the song fields and the artist fields for all the artists for a chosen song. More realistically the song and artist tables would have many more columns, and the where clause be more involved and result in more than one song. But none of those things are relevent to the issue. Importantly you say >It has exactly the same query plan as the query with the view. That is not what I am getting. I have listed the query plans I get with 3.8.10.1, what version are you using and what query plans do you get? On 12 February 2016 at 13:34, Clemens Ladisch <clemens at ladisch.de> wrote: > Dave Blake wrote: > > I noticed my queries going very slowly after changing a join to a left > > join, examination of the query plan showed that the optimiser was > choosing > > a poor plan. > > It chooses a _correct_ plan. > > > It only occurs when the left join is on a views, if explicit > > tables are used instead then the problem does not occur. > > Your queries are quite different. (One less LEFT.) > > This is the equivalent query with tables: > > SELECT song.*, song_artist.*, artist.* > FROM song > LEFT JOIN (song_artist JOIN artist > ON song_artist.idArtist = artist.idArtist > ) AS songartistview > ON song.idSong = songartistview.idSong > WHERE song.idSong =1; > > It has exactly the same query plan as the query with the view. > > > In trying to get the artist(s) for a song ... > > A query to get the artist(s) for a song would look like this: > > SELECT * > FROM artist > WHERE idArtist IN (SELECT idArtist > FROM song_artist > WHERE idSong = 1); > > What is your query actually supposed to do? > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
