Arrrgh, Google Chrome ate the top half of my reply. You must also expose the number column in the inner query against PLAYLIST_SONG; include your number-condition there and also specify the number column in the select-list:
( select id_song, number from ( select id_song from PLAYLIST_SONG where id_playlist=2 {AND|OR } number > 258 ) as MYPLAYLISTSONGS Regards Tim Romano On Tue, May 11, 2010 at 12:39 PM, Tim Romano <tim.romano...@gmail.com>wrote: > 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