On 4 Sep 2012, at 3:56pm, sattu <linkpr...@rocketmail.com> wrote: > What I observed is, if the offset is very high like say 90000, then it takes > more time for the query to execute. Following is the time difference between > 2 queries with different offsets: > > > select * from myTable LIMIT 100 OFFSET 0 //Execution Time is less than > 1sec > select * from myTable LIMIT 100 OFFSET 95000 //Execution Time is almost > 15secs
These queries are not useful, since you have not specified an ORDER BY clause in your SELECT. If you don't specify an ORDER BY clause, then SQL is allowed to sort the records two different ways for two different SELECT commands, which would mean that some rows might appear in both queries, or and some not in either. If you intend to use OFFSET then you should be using ORDER BY too. > Can anyone suggest me how to optimize this query? I mean, the Query > Execution Time should be same and fast for any number of records I wish to > retrieve from any OFFSET. The problem is that use of OFFSET 95000 requires SQLite to do a search which /does/ include the first 95000 rows, then quickly run through 95000 rows and discard them before returning the first result to your program. Discarding the 95000 results you don't want is what's taking the time. I don't know how your program is written but it might be possible to use a key column to allow SQLite to find the right rows immediately. For example, suppose you were using a key column called 'rowid' for your SELECT command: SELECT * FROM myTable ORDER BY rowid LIMIT 100 To have this SELECT run quickly, you would make sure you had an index on your 'rowid' column. If your 'rowid' column is the real INTEGER PRIMARY KEY for the table, then SQLite will automatically create such an index for you without you having to ask for it. Then instead of using OFFSET to make SQLite discard 95000 results, you use WHERE to specify the records you're interested in. In your software you keep track of the last value for rowid returned and use that when writing the next command. So your first SELECT might be SELECT * FROM myTable WHERE rowid > -1 ORDER BY rowid LIMIT 100 and perhaps the last row returned by this has a rowid of 104. Then your next SELECT should be SELECT * FROM myTable WHERE rowid > 104 ORDER BY rowid LIMIT 100 and perhaps the last row returned by this has a rowid of 205. Then your next SELECT should be SELECT * FROM myTable WHERE rowid > 205 ORDER BY rowid LIMIT 100 If you use SELECT commands like this, SQLite can use the index on the rowid column to zoom straight to the first row it should return. It could do this in far less than a second, no matter how big your value for rowid is. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
