Re: [sqlite] select performance with join

Thanks for the explanation. I recall seeing posts suggesting the use of union instead of or, and thought "if it's that easy, why doesn't SQLite do it?" The optimizer documentation says: --- Suppose the OR clause consists of multiple subterms as follows: expr1 OR expr2 OR expr3 If every

Re: [sqlite] select performance with join

wrote in message news:20090508113252.2uqkghcsj6og8...@webmail.korg.it > Citando Igor Tandetnik : > >> Andrea Galeazzi wrote: >>> but when I execute: >>> >>> SELECT S.id,title,artist,bpm,name >>> >>> FROM Song AS S >>> >>> LEFT JOIN Genre

Re: [sqlite] select performance with join

"Jim Wilcoxson" wrote in message news:c5830b750905080813p6bf901bn526c81ca8ce7a...@mail.gmail.com > I don't know if it makes any difference, but is that where clause the > same as: > > WHERE name< 'zUmM' OR (name= 'zUmM' AND S.id< 8122) SQLite's optimizer cannot use an index for

Re: [sqlite] select performance with join

I don't know if it makes any difference, but is that where clause the same as: WHERE name< 'zUmM' OR (name= 'zUmM' AND S.id< 8122) The original way it was coded, all 3 conditions would have to be evaluated most of the time. The new way might get most rows with 1 condition. Depends on the data

Re: [sqlite] select performance with join

Citando Igor Tandetnik : > Andrea Galeazzi wrote: >> but when I execute: >> >> SELECT S.id,title,artist,bpm,name >> >> FROM Song AS S >> >> LEFT JOIN Genre AS G ON (S.genre_id = G.id) >> >> WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122) >> >> ORDER BY

Re: [sqlite] select performance with join

Andrea Galeazzi wrote: > but when I execute: > > SELECT S.id,title,artist,bpm,name > > FROM Song AS S > > LEFT JOIN Genre AS G ON (S.genre_id = G.id) > > WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122) > > ORDER BY name DESC, S.id DESC LIMIT 20; Note that LEFT JOIN is