On 11 May 2010 11:07, Andrea Galeazzi <galea...@korg.it> wrote: > Hi guys, > I'm in a bind for a huge time consuming query! . . . > The second case is about 35 times slower... so the scrolling is quite > impossible (or useless)! > SELECT song_number AS number,title FROM Song AS S, Playlist_Song AS PS > WHERE S.id = PS.id_song AND > PS.id_playlist = 2 AND genre_id = 0 AND artist = 'Las ketchup' > AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke > version)' OR number > 959) > ORDER BY title ASC , number ASC LIMIT 4; > > I also execute the EXPLAIN QUERY PLAN: > 1st query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY > > 2nd query: 0 0 TABLE Song WITH INDEX Song_Browse_View_idx ORDER BY > 1 1 TABLE Playlist_Song AS PS > So it seems that the second plan (1,1) requires very long time! > How can I optimized a such kind of query?
You can see that there is no index being used for looking up data on table Playlist_Song. A good first step to improve performance is to add an index that will be used for this query: create index playlistSong_id_song on Playlist_Song( id_song ); or create index playlistSong_id_playlist on Playlist_Song( id_playlist ); > Cheers > Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
