PHP/MySQL: using LIMIT on an ORDERed BY query

How can I use the LIMIT funtion of MySQL AFTER it has been ORDERed BY

[reynaerde]

Hello, I hope this question has not been asked before but I could not find it anywhere on the forum. My question is the following:

I want to spread the contents of my MySQL DB over different pages(i.e. page 1,2,etc).
First I thought I could use LIMIT 0,9 and then on the next page LIMIT 9,18. Although this works, I also need to have it ordered using the ORDER BY statement.
From the documentation I read the LIMIT X statement only considers the first X results found from the WHERE clause(so, the ORDER BY statement only orders the already retrieved X results, instead of the whole result).

So, I need to return the first 9 results from the ordered results of the MySQL query.
What is the most efficient way to do this?
I could create different tables, each one ordered by some criterium but this creates quite some redundancy.

I hope you can help me :)

[ergophobe]

Welcome WebmasterWorld reynaerde.

In fact, MySQL applies the ORDER BY first, then the LIMIT, so your method will work just fine. Try it.

Tom

[coopster]

Welcome to WebmasterWorld, reynaerde!

Rest assured, MySQL will

SELECT

your records first,

ORDER

them, then take the

LIMIT

.

You are probably reading How MySQL Optimizes LIMIT [dev.mysql.com] which lets you in on some of the internal workings of MySQL. Basically, it is saying that, yes, I am going to sort your result set in the order you requested, but as I start the order sorting, I'm going to keep in mind your limitations and apply them at the same time and stop as soon as I meet your needs.

Thanks, ergophobe ;)

[reynaerde]

Ah, yes, I thought it was not working correctly but thanks to your answer I looked at the code again and discovered it was something else causing the error. Yes, I was reading the 'How MySQL Optimizes LIMIT' :)
Thanks a lot to the both of you for your fast replies!