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
