Hi,
I am having 40000 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);
ArtistId ArtistName 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 Track YomiTrack URL Album_Id Artist_Id
AlbumArtist_Id
1 trak1 TRAK1 c:/trak1 22 10 1
2 song SONG c:/song 21 11 2
3 abc ABC c:/abc 23 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
