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
