You could remove the title condition from the inner SONGS select, limiting your conditions to artist and genre; an index on column [artist] would make this subquery run quickly:
( select id_song from SONG where genre_id = 0 AND artist = 'Las ketchup' // AND title >= 'Asereje(karaoke version)' // --> moved to outer select > ) as MYSONGS The goal is to produce small inner subsets using indexes, and then to join these with each other, and to let the inner subsets expose the necessary columns to the outer query. Regards Tim Romano On Tue, May 11, 2010 at 11:13 AM, Andrea Galeazzi <galea...@korg.it> wrote: > 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 <tim.romano...@gmail.com>: > > > 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 > > > > _______________________________________________ > 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
