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.
On Thu, Dec 13, 2012 at 2:34 PM, Johan De Meersman <vegivamp@stripped>wrote:
> ----- Original Message -----
> > From: "Akshay Suryavanshi" <akshay.suryavanshi50@stripped>
> > 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.
> 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.