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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users