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 avoid the left join by ensuring that all songs have at least one artist. This is a better data design in the end. On 14 February 2016 at 18:00, Clemens Ladisch <clemens at ladisch.de> wrote: > 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 <http://www.sqlite.org/optoverview.html#flattening>; the > optimizer has problems when you use a subquery (i.e., a view) at the > right side of a left join. > > If you really want to keep the view, you could replace the outer join > with an inner join, and add the non-matching rows separately: > > SELECT A.*, view.* FROM A JOIN view ON ... WHERE ... > UNION ALL > SELECT A.*, NULL FROM A WHERE ... AND id NOT IN (SELECT A_id FROM view); > > Whether this is better than breaking up the view is something you have > to decide yourself. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
