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 try this example. CREATE TABLE song ( idSong integer primary key, strTitle varchar(512)) CREATE TABLE song_artist ( idArtist integer, idSong integer) CREATE TABLE artist (idArtist integer primary key, strArtist varchar(256), strBio text) CREATE INDEX idxSongArtist_1 ON song_artist ( idSong); CREATE INDEX idxSongArtist_2 ON song_artist ( idArtist); CREATE VIEW songartistview AS SELECT song_artist.idSong AS idSong, song_artist.idArtist AS idArtist, artist.strArtist AS strArtist FROM song_artist JOIN artist ON song_artist.idArtist = artist.idArtist In the data there can be songs with no artist, and artists with no song (hence the use of left join) Compare the query plan of 1) SELECT song.*, songartistview.* FROM song LEFT JOIN songartistview ON song.idSong = songartistview.idSong WHERE song.idSong =1 with 2) SELECT song.*, song_artist.*, artist.* FROM song LEFT JOIN song_artist ON song.idSong = song_artist.idSong LEFT JOIN artist ON song_artist.idArtist = artist.idArtist WHERE song.idSong =1 Query 2) sensibly does "SEARCH TABLE song USING INTEGER PRIMARY KEY (rowid=?)" "SEARCH TABLE song_artist USING INDEX idxSongArtist_1 (idSong=?)" "SEARCH TABLE artist USING INTEGER PRIMARY KEY (rowid=?)" But the view equivalent 1) does "SCAN TABLE song_artist" "SEARCH TABLE artist USING INTEGER PRIMARY KEY (rowid=?)" "SEARCH TABLE song USING INTEGER PRIMARY KEY (rowid=?)" "SCAN SUBQUERY 1" In trying to get the artist(s) for a song, scanning the full song_artist table is not an efficient place to start! Note this is a greatly simplified example to show the issue, in real application the impact of a suboptimal plan is significant. My testing was done mostly in v3.8.10.2, but this also happens in v3.8.6, yet does not seem to occur in v3.8.3.1 Running ANALYZE on my real world data made the issue even worse (plan started with scan of artist table instead). My current work around is to convert my views into tables, but it would be nice to be able to use views as an alias for some table joins. I did consider using "CROSS JOIN" because SQLite effectively treats that as a LEFT JOIN but does not left the optimiser change the table order, however that makes my SQL non-generic and my queries need to work on MySQL too.
