<[email protected]> wrote in message news:[email protected] > Citando Igor Tandetnik <[email protected]>: > >> Andrea Galeazzi <[email protected]> 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. >> > I replaced LEFT JOIN with JOIN but it got worse
This means that the majority of all records in Song table satisfy the condition of name<= 'zUmM'. Basically, your query leaves SQLite no choice but to scan all records in Song. I don't see how it could be made to work any faster (without significant redesign of the schema - e.g. moving genre name into Song table). > But I think I need to use LEFT JOIN because I have also > to accept the records with S.genre_id = NULL. Well, if you need such records, then you need a different query. The one you show doesn't return these records, despite using LEFT JOIN. Don't take my word for it, test it on a small sample database. > 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. Again, this means that the condition (name<= 'zUmM') matches a large number of records in Song. When not ordering by S.id, SQLite can do the following: scan Genre table starting from 'zUmM' and going down, using an index on Genre(name). For each record in Genre, retrieve corresponding records in Song using an index on Song(genre_id). As soon as it got 20 records, it can stop. If you also order on S.id, SQLite must continue the process described above until it retieves _all_ records, sort them, and then return top 20. Run your queries (in sqlite3 command line utility or your favorite management tool) with EXPLAIN QUERY PLAN prepended. You may find the results illuminating. Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
