On Mon, Oct 10, 2011 at 10:27 AM, cricketfan <srtedul...@yahoo.co.in> wrote:
> > SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC; > > Since I have the index on PK1, I believe the rows will be returned in the > ORDER of PK1. Putting an ORDER BY clause will be a no-op. > Do you think otherwise? > There is no guarantee of this. Without the ORDER BY clause, SQLite might return the rows in PK1 order, or it might not. The answer depends on what other indices are available, whether or not you have run ANALYZE, the distribution of values for PK1 in your table, what PRAGMAs you might have run, and which version of SQLite you are using. If you do include the ORDER BY clause, SQLite will probably arrange for the answer to come out in PK1 order by default, and thus avoid doing any sorting on the result. ORDER BY is an expensive operation, and so the query optimizer in SQLite works hard to turn it into a no-op. But you still need to include the ORDER BY clause in your query so that SQLite knows that it is not free to choose a different query plan that outputs PK1 in some other order. > > > Gabríel "A." Pétursson wrote: > > > > Be aware that if you do not specify an ORDER BY clause, the order of the > > returned rows are undefined. You might not even end up with rows with a > > primary key even near 100. > > > > What you probably want is: > > SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC; > > > > Other than that, those two queries should be near identical in your > > situation. Mind the gaps. > > > > On 10/07/2011 03:24 PM, cricketfan wrote: > >> Any advice would be greatly appreciated. > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://old.nabble.com/SQLITE-LIMIT-clause-tp32607006p32624793.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
