Advanced MySQL 8

Case study 2 – how to optimize sort indexes

Did you know that performance problems are often due in particular to MySQL's ORDER BY and LIMIT?

Here's what you need to know about optimizing MySQL's ORDER BY and LIMIT to avoid problems.

MySQL's ORDER BY and LIMIT constitute the most common use of ORDER BY in web and enterprise applications, with large datasets that are sorted.

For example, on many websites, you will sort top tags, users who have recently subscribed, and so on, which requires using ORDER BY and LIMIT in the background on the server.

This type of ORDER BY generally does something like the following:

SELECT ... .. WHERE [conditions] ORDER BY [sort] LIMIT N1, M1

As I often recommend, you need to make sure that your queries use indexes. ...