Boost MySQL Queries: How GaussDB’s Limit‑Offset Pushdown Cuts Execution Time

1. Background

In the community edition of MySQL, a SELECT statement with LIMIT OFFSET returns all rows that satisfy the WHERE clause to the SQL layer, which then filters out the offset rows. As the offset grows, query time increases significantly, and when the offset reaches millions, the latency often exceeds acceptable limits.

SELECT * FROM lineitem LIMIT 10000000,10;</code><code>SELECT * FROM lineitem LIMIT 10 OFFSET 10000000;

2. Principle

GaussDB (for MySQL) adds a Limit‑Offset pushdown strategy, delegating the offset computation to the InnoDB storage engine. This avoids transmitting rows within the offset range to the SQL engine and accelerates queries in two ways:

Reduces multiple interactions between InnoDB and the SQL layer.

When a secondary index is used and a table‑row lookup is required, early offset filtering eliminates the back‑table cost.

The optimization consists of two components:

Offset Pushdown (OP) : Enables InnoDB to skip rows within the offset range and return only the required rows.

Redundant Condition Removal (RCR) : Removes redundant checks that the storage engine has already performed, allowing OP to apply to more queries.

2.1 Offset Pushdown

When OP is enabled, the SQL layer evaluates whether the offset can be pushed down. If so, it passes the offset value to InnoDB, which directly skips rows in the offset range and returns subsequent rows, saving interaction time and avoiding unnecessary back‑table lookups for non‑covering index scans.

OP is effective only when the WHERE clause can be fully pushed down to the storage engine and evaluated using an index.

2.2 Redundant Condition Removal

RCR ensures the SQL layer recognizes that the storage engine has already performed certain condition checks, preventing duplicate evaluations. It works together with OP, similar to Index Condition Pushdown (ICP), by first eliminating redundant conditions before ICP executes.

3. Scenario Constraints

Only single‑table SELECT queries on InnoDB tables are supported.

All WHERE conditions must be fully pushable to the engine.

Features such as SELECT DISTINCT, HAVING, GROUP BY, ROLLUP, aggregate functions, WINDOW functions, and file sorting are not supported.

Multi‑partition table queries are not supported.

RCR supports <, >, =, <=, >=, BETWEEN, IFNULL.

4. Process Flow

During the optimizer phase, the SQL layer checks whether the query meets the conditions for Limit‑Offset pushdown. If it does, the offset value is set to 0 for the SQL layer, and the InnoDB layer is instructed to skip the specified number of rows. In InnoDB, the row_search_mvcc function evaluates the pushed‑down offset and skips rows accordingly.

When RCR is active, the SQL layer knows that the returned rows have already been filtered, extending the applicability of OP.

5. Usage

The feature can be enabled via a system switch or by using hints: OFFSET_PUSHDOWN(table_name) – enable pushdown. NO_OFFSET_PUSHDOWN(table_name) – disable pushdown.

Example with hint:

EXPLAIN SELECT /*+ OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10;

6. Performance Comparison

Using the TPC‑H benchmark (scale factor 10 GB), three query scenarios were tested on the lineitem table.

Results show that enabling Limit‑Offset pushdown improves query performance dramatically: Q1 gains 5.56×, Q2 gains 33.07×, and Q3 gains 33.02×.

7. Conclusion

This article introduced GaussDB (for MySQL)’s Limit‑Offset pushdown optimization, which addresses performance issues of queries with large offsets by moving offset processing to the storage engine, reducing data transfer and back‑table overhead, and delivering substantial speedups.