MY Table is:
"CREATE TABLE ARTIST(ArtistId INTEGER PRIMARY KEY,ArtistName TEXT NOT NULL
COLLATE NOCASE, ArtistTrackCount INTEGER, UNIQUE(ArtistName));"
"CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY,AlbumName TEXT NOT NULL
COLLATE NOCASE,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"
"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY,Track TEXT NOT NULL,URL TEXT NOT
NULL,Album_Id INTEGER,Artist_Id INTEGER);"
"CREATE UNIQUE INDEX ARTIST_idx ON ARTIST (ArtistName);"
"CREATE UNIQUE INDEX ALBUM_idx ON ALBUM (AlbumName);"
"CREATE INDEX MUSIC_idx ON MUSIC (Track);"
"CREATE INDEX MUSIC_ARTIST_idx ON MUSIC (Artist_Id);"
"CREATE INDEX MUSIC_ALBUM_idx ON MUSIC (Album_Id);"
"CREATE INDEX MUSIC_ARTIST_ALBUM_idx ON MUSIC (Artist_Id, Album_Id);"
I am using
First 10 --> "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;"
Next 10 ie., 11 to 20 --> "SELECT * FROM ARTIST WHERE ArtistName > ? ORDER
BY ArtistName LIMIT 10 ;"
Previous 10 -->"SELECT * FROM ARTIST WHERE ArtistName < ? ORDER BY
ArtistName DESC LIMIT ? ;"
The above statements provides best performance .i have used < ,> since I
want the results to be in ORDER BY.
If I don't want to use ORDER BY then how can I optimize without using
OFFSET.
If I am using OFFSET then it more time.i am having 60000 records.
Is there any other way to optimize.
Another doubt is :
For this querry "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" will
it use ARTIST_idx or not.
Thanks & Regards,
Mahalakshmi
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
