1. Try discrete single-column indexes rather than multi-column composite indexes. 2. Try breaking the query down into subsets expressed as parenthetical queries; you can treat these parenthetical queries as if they were tables by assigning them an alias, and then you can join against the aliases. I have sped queries up in SQLite using this approach and, with a little tinkering, the time can drop from over a minute to sub-second. Performance will depend on the indexes and criteria used, of course. But this approach lets you see how SQLite is optimizing the creation of the component sets from which you can build up your ultimate query. . select * from SONG JOIN
( select id_song from ( select id_song from PLAYLIST_SONG where id_playlist=2 ) as MYPLAYLISTSONGS JOIN ( select id_song from SONG where genre_id = 0 AND artist = 'Las ketchup' AND title >= 'Asereje(karaoke version)' ) as MYSONGS on MYSONGS.id_song = MYPLAYLISTSONGS.id_song ) as SONGIDLIST on SONG.id_song = SONGIDLIST.id_song Regards Tim Romano On Tue, May 11, 2010 at 6:07 AM, Andrea Galeazzi <galea...@korg.it> wrote: > Hi guys, > I'm in a bind for a huge time consuming query! > I made the following database schema: > > CREATE TABLE Song ( > id INTEGER NOT NULL UNIQUE, > title VARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, > artist VARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE, > genre_id INT NOT NULL DEFAULT 0, > PRIMARY KEY (id), > > CONSTRAINT fk_Genre FOREIGN KEY (genre_id) > REFERENCES Genre (id) > ON DELETE SET DEFAULT > ON UPDATE CASCADE); > > CREATE INDEX Song_Browse_View_idx ON Song(genre_id,artist,title); > > CREATE TABLE PlayList ( > id INTEGER NOT NULL UNIQUE, > name VARCHAR(15) NOT NULL COLLATE NOCASE, --KORGCOLLATE, > length INT NOT NULL DEFAULT 0, > created_date TEXT, > PRIMARY KEY (id)); > > CREATE TABLE PlayList_Song ( > id_song INT NOT NULL, > id_playlist INT NOT NULL, > song_number INTEGER NOT NULL, > PRIMARY KEY (id_playlist, song_number), > CONSTRAINT fk_PlayList_Song1 FOREIGN KEY (id_song) > REFERENCES Song (id) > ON DELETE CASCADE > ON UPDATE CASCADE, > CONSTRAINT fk_PlayList_Song2 FOREIGN KEY (id_playlist) > REFERENCES PlayList (id) > ON DELETE CASCADE > ON UPDATE CASCADE); > > CREATE INDEX PlayList_Song_song_number_idx ON PlayList_Song(song_number); > > Now I need to scroll title filtered by genre_id and artist both in Song > table and Playlist. > The query for the first case is very fast: > SELECT id AS number,title FROM Song WHERE genre_id = 0 AND artist = > 'Las ketchup' > AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke > version)' OR number > 258) > ORDER BY title ASC , number ASC LIMIT 4; > > 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? > Cheers > > _______________________________________________ > 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
