Order of execution of ORDER BY and LIMIT in a MySQL query

Question

Hello all

I have a query like this where I want to display sorted records in a paginated format.

This is my query.

SELECT * FROM answers
WHERE userid = '10'
ORDER BY votes LIMIT 10, 20

The two arguments to LIMIT will be used for generating page-by-page display of records. Now, my problem is ORDER BY. How does MySQL execute this query?

1st way

• Select the records according to filters
• Sort them
• Limit them

2nd way

• Select the records according to filters
• Limit them
• Sort them

If I think like MySQL engine, I would want to implement 2nd, since I would then have to sort lesser records?

Can somebody throw some light on this?

Regards

Answer 1

Mysql follows the 1st way.

Answer 2

A SQL LIMIT will in all databases always work on the result of a query, so it will run the query with the ORDER BY and that result will then be limited. This is a functional requirement, the database not perse needs to execute it like that.

Thus the 1st way.

Answer 3

The 1st way as mentioned by the last two answers. From a user perspective, the 2nd way won't be very useful anyways.