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