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 distribution of course. Jim On 5/8/09, galea...@korg.it <galea...@korg.it> wrote: > Citando Igor Tandetnik <itandet...@mvps.org>: > >> Andrea Galeazzi <galea...@korg.it> 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 pointless here, since any record with G.name=NULL >> won't make it past the WHERE clause. Replace it with plain JOIN, you >> should see an improvement. >> >> Igor Tandetnik >> > I replaced LEFT JOIN with JOIN but it got worse, now the the time is > about 8700 ms! But I think I need to use LEFT JOIN because I have also > to accept the records with S.genre_id = NULL. > I also tried this query: > “SELECT S.id,title,artist,bpm,name " > "FROM Song AS S, Genre AS G " > "WHERE (S.genre_id = G.id) AND (name<= 'zUmM') AND (name< 'zUmM' OR > S.id< 8122) " > "ORDER BY name DESC, S.id DESC LIMIT 20"; > even if it doesn't work for me because it doesn't match S.genre_id = > NULL, I noticed a little improvement to 6000 ms. Then I delete S.id > DESC and the performance has been abruptly increased to 400 ms. > Anyway probably the right statement is LEFT JOIN but how can I > optimize this kind of task? > Is it really an hard work or does it depend on my no knowledge about sqlite? >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
