Well Johan, I was referring to a condition when there is no index on the tables, not even primary keys. Your explanation makes complete sense about the optimizer and the pagination queries.
Thanks, Akshay S On Thu, Dec 13, 2012 at 2:34 PM, Johan De Meersman <vegiv...@tuxera.be>wrote: > > > ----- Original Message ----- > > From: "Akshay Suryavanshi" <akshay.suryavansh...@gmail.com> > > > > I am not sure, but if its a MyISAM table, it should be ordered by the > > records insertion order, and in case of InnoDB it should be ordered > > by the clustered index, not necessarily it should be a defined one. > > No. > > The optimizer may choose to do a full table scan, or it may choose to use > an index scan. That decision may change due to changes in the data, or > because the next version of mysql you upgrade to has different (and > hopefully better...) alghorithms, et cetera. > > The ONLY way to ensure consecutive queries return your data in the same > order, is specifying an order by clause. > > Apart from that, I personally prefer to avoid the limit 0,10 /limit 11/20 > technique, because a) rows might have gotten inserted and/or deleted, and > b) limit is applied to the full resultset. > > Instead, order by the PK (or another unique index or combination of > indices), remember the last record's value(s) and use that as starting > point for your next query. > > > > -- > Unhappiness is discouraged and will be corrected with kitten pictures. >
