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

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

yes, it will -- it will return different rows than LIMIT 1,5

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

what happened when you tested that?

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

you are not wrong, that excerpt from the mysql.com web site is correct

read it carefully, to understand how it really works