Sorry but in your solution, how can I solve the condition AND title >= 'Asereje(karaoke version)' AND (title > 'Asereje(karaoke >> version)' OR number > 258) ? title is on song and number is song_number on Playlist_Song AS PS. Furthermore I also need title and number in place of your select * from SONG Could you write it again please? Thanks
Citando Tim Romano <[email protected]>: > 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 <[email protected]> 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 >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
