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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
