Re: [sqlite] join performance query

[Tim Romano]

Let's try that again : expose the [number] column to the outer selection (**
are for emphasis**):


( select  id_song, **number** from


 (
 select  id_song, **number**
 from PLAYLIST_SONG
 where id_playlist=2
{and|or }  number > 258
) as MYPLAYLISTSONGS

Regards
Tim Romano

On Tue, May 11, 2010 at 12:46 PM, Tim Romano wrote:

> And you would put move your title-condition to the outer query:
>
> .
> .
> .
>  ) as SONGIDLIST
>  on SONG.id_song = SONGIDLIST.id_song
>
> where
> your title-condition and|or your  title-number condition
>
>
> Regards
> Tim Romano
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] join performance query

[Tim Romano]

And you would put move your title-condition to the outer query:

.
.
.
 ) as SONGIDLIST
 on SONG.id_song = SONGIDLIST.id_song

where
your title-condition and|or your  title-number condition


Regards
Tim Romano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] join performance query

[Tim Romano]

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 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 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 :
>>
>> > 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 
>> wrote:
>> >
>> >> Hi guys,
>> >> I'm in a bind for a huge time consuming query!
>> >> I made the following database schema:
>> >>
>> >> CREATE TABLE Song (
>> >>idINTEGER NOT NULL UNIQUE,
>> >>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>> >>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE
>> NOCASE,
>> >>genre_idINT 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 

Re: [sqlite] join performance query

[Tim Romano]

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  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 :
>
> > 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 
> wrote:
> >
> >> Hi guys,
> >> I'm in a bind for a huge time consuming query!
> >> I made the following database schema:
> >>
> >> CREATE TABLE Song (
> >>idINTEGER NOT NULL UNIQUE,
> >>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
> >>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
> >>genre_idINT 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 

Re: [sqlite] join performance query

[Andrea Galeazzi]

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 :

> 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  wrote:
>
>> Hi guys,
>> I'm in a bind for a huge time consuming query!
>> I made the following database schema:
>>
>> CREATE TABLE Song (
>>idINTEGER NOT NULL UNIQUE,
>>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>>genre_idINT 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

Re: [sqlite] join performance query

[Tim Romano]

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  wrote:

> Hi guys,
> I'm in a bind for a huge time consuming query!
> I made the following database schema:
>
> CREATE TABLE Song (
>idINTEGER NOT NULL UNIQUE,
>titleVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>artistVARCHAR(40) NOT NULL DEFAULT '' COLLATE NOCASE,
>genre_idINT 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

Re: [sqlite] join performance query

[Simon Davies]

On 11 May 2010 11:07, Andrea Galeazzi  wrote:
> Hi guys,
> I'm in a bind for a huge time consuming query!
.
.
.
> 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?

You can see that there is no index being used for looking up data on
table Playlist_Song. A good first step to improve performance is to
add an index that will be used for this query:
  create index playlistSong_id_song on Playlist_Song( id_song );
or
  create index playlistSong_id_playlist on Playlist_Song( id_playlist );

> Cheers
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users