On Thu, 17 Apr 2008 12:47:52 +0530, Mahalakshmi.m wrote: >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.
That is because for every logical page you present to the user (in a scrolling window), <offset> number of rows have to be retrieved and discarded before the query can return rows in the region of interest. >i am having 60000 records. >Is there any other way to optimize. Have a look at http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for how and why to optimize a scrolling list. >Thanks & Regards, >Mahalakshmi -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
