Analysis of low performance caused by large offset of LIMIT in MySQL query

Preface

As we all know, mysql query uses select command, and limit and offset parameters can read records in a specified range, but offset is too large to affect the query performance and its optimization method

Paging is inevitable in our business system. When you think of paging, you will definitely think of using LIMIT in SQL to achieve it. However, if the incorrect use of LIMIT will lead to performance problems (SQL executes very slowly and may bring down the server), it will also be approved by the leader; So, let's look at how to use LIMIT correctly.

The following words are not much to say, let's take a look at the detailed introduction

LIMIT OFFSET, ROW_COUNT Paging

Ways of Performance Problems

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

The person who wrote this SQL statement must have thought this: The MySQL database will directly locate the 10000000 bits that meet the requirements, and then take 30 pieces of data.
However, in fact, MySQL does not work like this.

LIMIT 1000000, 30 means: Scan 1000030 rows that meet the criteria, throw away the previous 1000000 rows, and return to 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:

Subquery only used index column, did not take the actual data, so does not involve the disk IO, so even a relatively large offset, the query speed will not be too bad.

For those who are interested in specific principle analysis, please see 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