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