List:General Discussion« Previous MessageNext Message »
From:Akshay Suryavanshi Date:December 13 2012 9:31am
Subject:Re: using LIMIT without ORDER BY
View as plain text  
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 <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.
>
> 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