7

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

vikmalhotra
  • 9,981
  • 20
  • 97
  • 137

4 Answers4

6

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.

Kdeveloper
  • 13,679
  • 11
  • 41
  • 49
2

Mysql follows the 1st way.

zerkms
  • 249,484
  • 69
  • 436
  • 539
1

If you want your 2nd way, try using a subquery with the limit, then order.

Something like:

SELECT * FROM
(
  SELECT * FROM answers
  WHERE userid = '10'
  LIMIT 10
)
ORDER BY votes
Koopakiller
  • 2,838
  • 3
  • 32
  • 47
Geo
  • 11
  • 1
0

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

kasgoku
  • 5,057
  • 5
  • 20
  • 20