Re: [sqlite] Indexing and Search speed

[dcharno]

From: Dennis Cote <[EMAIL PROTECTED]>
> No, that's not true. A sub-query is like any other query. I have 
> rearranged the query to make it more readable.
> 
>  select types.Track,types.URL
>  from ALBUM
>  inner join (select * from MUSIC where Artist_Id =?) as types
>  on ALBUM.AlbumId=types.Album_Id
>  order by ALBUM.YomiAlbumName;
> 
> In this case the sub-query is returning the subset of the Music table by 
> the specified artist. This result table is given the name "types", and 
> it is joined to the album table.

I didn't know this was possible -- my trusty "learn SQL in 10 minutes" has 
failed me. But it makes sense from your explanation.  Thanks.

In most cases like this, I've 'hidden' the join inside a view so the syntax is 
clean.  But, I expect moving the filtering before the join is much more 
efficient, no?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexing and Search speed

[Dennis Cote]

dcharno wrote:
>> select types.Track,types.URL from ALBUM inner join (select * from MUSIC
>> where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by
>> ALBUM.YomiAlbumName ;
> 
> How does the subquery work in this statement?   I thought subqueries 
> could only retrieve a single column.

No, that's not true. A sub-query is like any other query. I have 
rearranged the query to make it more readable.

 select types.Track,types.URL
 from ALBUM
 inner join (select * from MUSIC where Artist_Id =?) as types
 on ALBUM.AlbumId=types.Album_Id
 order by ALBUM.YomiAlbumName;

In this case the sub-query is returning the subset of the Music table by 
the specified artist. This result table is given the name "types", and 
it is joined to the album table.

It is often the case the you use a sub-query to select a single result 
value (i.e. one row and one column) for use in a comparison, but there 
is no restriction on what they can return.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexing and Search speed

[dcharno]

> select types.Track,types.URL from ALBUM inner join (select * from MUSIC
> where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by
> ALBUM.YomiAlbumName ;

How does the subquery work in this statement?   I thought subqueries 
could only retrieve a single column.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Indexing and Search speed

[Mahalakshmi.m]

Hi,

I am having 4 records in my database.
I am using Joins method.

My Table Looks like:

"PRAGMA encoding = UTF16;" 

"CREATE TABLE ALBUMARTIST (AlbumArtistId INTEGER PRIMARY KEY NOT NULL,
AlbumArtistName TEXT NOT NULL COLLATE NOCASE, YomiAlbumArtistName TEXT NOT
NULL, UNIQUE (AlbumArtistName));"

CREATE UNIQUE INDEX ALBUMARTIST_idx ON ALBUMARTIST (YomiAlbumArtistName);

AlbumArtistId   AlbumArtistName YomiAlbumArtistName
1   zzz/bbb ZZZ/BBB
2   ccc/xxx CCC/XXX
3   bbb/aaa BBB/AAA

"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"

CREATE UNIQUE INDEX ARTIST_idx ON ARTIST (YomiArtistName);

ArtistIdArtistName  YomiArtistName  
10  bbb BBB
11  xxx XXX
12  aaa AAA

"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));"

CREATE UNIQUE INDEX ALBUM_idx ON ALBUM (YomiAlbumName);

AlbumId AlbumName   YomiAlbumName
20  zzz ZZZ
21  ccc CCC
22  bbb BBB

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
INTEGER,AlbumArtist_Id INTEGER);"

CREATE UNIQUE INDEX MUSIC_idx ON MUSIC (YomiTrack);

Id TrackYomiTrack   URL   Album_Id  Artist_Id
AlbumArtist_Id
1  trak1TRAK1 c:/trak1  22   10   1
2  song SONG  c:/song   21   11   2
3  abc  ABC   c:/abc23   12   3

I am using the following Searching Statements.

"SELECT * FROM ARTIST ORDER BY YomiArtistName;"  
Idx -> YomiArtistName

"SELECT * FROM ALBUMARTIST ORDER BY YomiAlbumArtistName;"
Idx -> YomiAlbumArtistName

SELECT Track,YomiTrack,URL FROM MUSIC ORDER BY YomiTrack;
Idx -> YomiTrack

SELECT AlbumId,AlbumName,YomiAlbumName FROM ALBUM ORDER BY YomiAlbumName;
Idx -> YomiAlbumName

Am I rightly using Indexing or not needed.
To speed up the Search Statements Below Do I want to any Indexing .If so for
which Fields I have to Add.

select DISTINCT ALBUM.AlbumId,ALBUM.AlbumName,ALBUM.YomiAlbumName from ALBUM
inner join (select * from MUSIC where Artist_Id =?) types on
ALBUM.AlbumId=types.Album_Id order by ALBUM.YomiAlbumName;

select types.Track,types.URL from ARTIST inner join (select * from MUSIC
where Album_Id =?) types on ARTIST.ArtistId=types.Artist_Id order by
ARTIST.YomiArtistName;

select MUSIC.Track,MUSIC.URL from MUSIC inner join ARTIST on
ARTIST.ArtistId=MUSIC.Artist_Id order by ARTIST.YomiArtistName ;

select types.Track,types.URL from ALBUM inner join (select * from MUSIC
where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by
ALBUM.YomiAlbumName ;

SELECT Track,URL FROM MUSIC WHERE Artist_Id = ? and Album_Id = ?;

select MUSIC.Track,MUSIC.URL from MUSIC inner join ALBUM on
MUSIC.Album_Id=ALBUM.AlbumId order by ALBUM.YomiAlbumName;

SELECT Track,URL FROM MUSIC WHERE AlbumArtist_Id = ?;

I am Using this for real time Application and my processor speed is 400MHz.
Foe Each Search I will get 100 results and Using the "Scrolling Cursor
Method" I will retrive the next for Sorted Fields.

Please help to increase my performance speed.

Thanks & Regards,
Mahalakshmi



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