Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    27

    Question Unanswered: order by with limit

    Hello All,

    i got a general question working with mysql:

    when running this:
    select * from my_table order by id desc limit 1,5

    will it not select the first 5 records, and then sort them by id (desc) ? means it will not trully retireve the top 5 records

    or will it truelly retrieve the top 5 ? means it will sort the whole table, and then retreive the top 5

    lets assume some test cases:
    - table is indexed using id (desc)
    - table is indexed using id (not desc) - means it will need to resort the table to desc in order to work
    - table is not sorted id

    thanks
    Chanan

    PS: will it make any diffrence if I would have run this:
    select * from my_table order by id desc limit 5,10

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bchanan View Post
    will it not select the first 5 records, and then sort them by id (desc) ?
    no

    if it doesn't sort them first, then how does it figure out which ones are "first" ??

    the ORDER BY happens first, then the LIMIT is applied

    Quote Originally Posted by bchanan View Post
    PS: will it make any diffrence if I would have run this:
    select * from my_table order by id desc limit 5,10
    yes, it will -- it will return different rows than LIMIT 1,5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    27

    question

    Hi,

    thanks for the quick answer.
    Should we not do:

    select * from
    ( select * from my_table order by id desc) my_table_sorted limit 1,5

    so i was told to do, when running on oracle / sybase
    (same idea differenct syntax)
    Chanan

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bchanan View Post
    Should we not do:
    what happened when you tested that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    27

    also see quate

    quate:
    "•If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so. "

    found on MySQL web site: MySQL :: MySQL 5.0 Reference Manual :: 7.3.1.15 LIMIT Optimization

    which shows that soon it found the top X records, it will stop and start sorting.
    Correct me if I am wrong
    Chanan

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are not wrong, that excerpt from the mysql.com web site is correct

    read it carefully, to understand how it really works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •