How to Optimize MySQL Queries with Large OFFSET in LIMIT Clause?

Improving MySQL Performance with Large OFFSET in LIMIT Clause

Facing performance degradation while applying a significant offset in the LIMIT clause of a MySQL SELECT query? This issue arises when the offset, denoted by 'm,' surpasses a certain threshold, such as 1,000,000, resulting in sluggish query execution.

While it's essential to use the LIMIT clause for specific table scenarios, there's a way to optimize performance without resorting to alternatives like filtering based on ID range ('id > 1,000,000 limit n').

Indexing Table-Based Optimization

Consider creating an indexing table that establishes a sequential relationship to the primary key of the target table. This indexing approach can significantly improve query performance.

1. Create Indexing Table:

   CREATE TABLE seq (
      seq_no int not null auto_increment,
      id int not null,
      primary key(seq_no),
      unique(id)
   );

2. Populate Sequence:

   TRUNCATE seq;
   INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;

3. Optimized Query using Indexing:
   To fetch 1000 rows from an offset of 1,000,000:

   SELECT mytable.*
   FROM mytable
   INNER JOIN seq USING(id)
   WHERE seq.seq_no BETWEEN 1000000 AND 1000999;

By leveraging the indexing table, this revised query bypasses sequential scanning and efficiently retrieves the desired rows based on the offset, significantly enhancing performance.

The above is the detailed content of How to Optimize MySQL Queries with Large OFFSET in LIMIT Clause?. For more information, please follow other related articles on the PHP Chinese website!