List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:December 13 2012 9:04am
Subject:Re: using LIMIT without ORDER BY
View as plain text  

----- 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.

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.
Thread
using LIMIT without ORDER BYjiangwen jiang13 Dec
  • Re: using LIMIT without ORDER BYAkshay Suryavanshi13 Dec
    • Re: using LIMIT without ORDER BYJohan De Meersman13 Dec
      • Re: using LIMIT without ORDER BYAkshay Suryavanshi13 Dec
        • Re: using LIMIT without ORDER BYJohan De Meersman13 Dec