[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: > Is there anything I can do to get the optimiser to perform 1) with the same > efficiency as 2)? See point 3 of ; the optimizer has problems when you use a subquery (i.e., a view) at the right side of a left join. If you really

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Thank you for your help. I now understand why the optimiser has difficulties and 1) is slower than 2) Thank you also for the union suggestion, although not sure in this case that it makes anything more readable than breaking the view into tables. In my real world application my solution is to avo

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

>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, i

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

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 JO

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

>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

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

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_arti

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

>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 necess

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

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

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

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 inst

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

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

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

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 only occurs when the left join is on a views, if explicit tables are used instead then the problem does not occur. To repeat the issue t