Analysis of the Poor Performance Caused by Large Offset of LIMIT in MySQL Query

Preface

We all know that MySQL query uses the select command, and with the limit and offset parameters, it can read records in a specified range. However, the reason why the offset is too large affects the query performance and the optimization method

We inevitably need paging in our business systems. When you think of paging, you will definitely think of using LIMIT in SQL to implement it. However, incorrect use of LIMIT can lead to performance issues (SQL execution is slow and may bring down the server) and may be criticized by your supervisor; so let's take a look at how to use LIMIT correctly.

Let’s take a look at the detailed introduction.

LIMIT OFFSET, ROW_COUNT to implement paging

Ways to have performance issues

SELECT * FROM myTable ORDER BY `id` LIMIT 1000000, 30

The person who wrote this SQL statement must have thought: the MySQL database will directly locate the 1,000,000th number that meets the conditions, and then fetch 30 pieces of data.
However, this is not how MySQL actually works.

LIMIT 1000000, 30 means: scan 1000030 rows that meet the condition, discard the first 1000000 rows, and then return the last 30 rows.

A better way

SELECT t.*
FROM (
  SELECT id
  FROM myTable
  ORDER BY
    id
  LIMIT 1000000, 30
  ) q
JOIN myTable t
ON t.id = q.id

The general principle is:

• The subquery only uses the index column and does not fetch actual data, so it does not involve disk IO. Therefore, even with a relatively large offset, the query speed will not be too slow.

Friends who are interested in the specific principle analysis can read this article: MySQL ORDER BY / LIMIT performance: late row lookups

postscript

To be continued.

References

• Why does MYSQL higher LIMIT offset slow the query down?
• MySQL ORDER BY / LIMIT performance: late row lookups

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.