LIMIT OFFSET Pushdown

Scenarios

In MySQL Community Edition, if you use LIMIT(N) and OFFSET(P) in a SELECT statement, the engine layer returns all rows that meet the WHERE condition to the SQL layer for processing. The SQL layer skip P rows of data and returns N rows of data. When secondary indexes are queried and columns in the primary table need to be accessed, the SQL layer must obtain column data from the tables. If OFFSET value (P) is much greater than the LIMIT value (N), the engine layer sends a large amount of data to the SQL layer for processing.

In TaurusDB, if you use LIMIT(N) and OFFSET(P) in a SELECT statement, data is pushed down to the engine layer for processing, speeding up queries.

Constraints

• Only SELECT statements for querying a single table are supported.
• All WHERE clauses in SELECT statements can be pushed down to the engine layer.
• SELECT DISTINCT, HAVING, GROUP BY, ROLLUP, aggregate functions, window functions, and file sorting are not supported.
• Queries on a partitioned table with multiple partitions are not supported.

How to Use

Examples

• Enabling LIMIT OFFSET pushdown by configuring the parameter: Using offset pushdown is displayed in the Extra column when you run EXPLAIN SQL to view an execution plan.

  EXPLAIN SELECT * FROM lineitem LIMIT 10000000,10;
  | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                 
  | 1  | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59281262 |   100.00 | Using offset pushdown 
  
  1 row in set, 1 warning (0.00 sec)

• Enabling LIMIT OFFSET pushdown using a hint

  EXPLAIN SELECT /*+ OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10;
  | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                
  | 1  | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59281262 |   100.00 | Using offset pushdown |
  1 row in set, 1 warning (0.00 sec)

• Disabling LIMIT OFFSET pushdown using a hint

  EXPLAIN SELECT /*+ NO_OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10;
  
  | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref | rows      | filtered | Extra |
  | 1 | SIMPLE       | lineitem | NULL       | ALL | NULL           | NULL | NULL    | NULL | 59281262 |   100.00 | NULL  |
  
  1 row in set, 1 warning (0.00 sec)

Performance Tests

• Run the following SQL statement (Q1) with no predicate conditions to access the primary table.

  EXPLAIN SELECT * FROM lineitem LIMIT 10000000,10;
  | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra   |
  | 1  | SIMPLE      | lineitem | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59281262 |   100.00 | Using offset pushdown |
  1 row in set, 1 warning (0.00 sec)

• Run the following SQL statement (Q2) with no predicate conditions to access the secondary index (including the index range conditions). Information about other columns needs to be obtained from the table.

  EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10;
  | id | select_type | table   | partitions | type | possible_keys                   | key         | key_len | ref | rows     | filtered | Extra                                       |
  
  | 1 | SIMPLE     | lineitem | NULL       | range | i_l_partkey_suppkey,i_l_partkey | i_l_partkey | 4       | NULL | 10949662 |   100.00 | Using offset pushdown; Using index condition |
  
  1 row in set, 1 warning (0.00 sec)

• Run the following SQL statement (Q3) with predicate conditions and ORDER BY to sort data by index.

  mysql> EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10;
  | id | select_type | table    | partitions | type | possible_keys                    | key         | key_len | ref | rows     | filtered | Extra                                       |
  
  | 1  | SIMPLE      | lineitem | NULL       | range | i_l_partkey_suppkey,i_l_partkey | i_l_partkey | 4       | NULL | 10949662 |   100.00 | Using offset pushdown; Using index condition |
  
  1 row in set, 1 warning (0.00 sec)

The following figure describes the performance of Q1, Q2, and Q3 when LIMIT OFFSET pushdown is enabled and disabled in the TPC-H Scale Factor 10 (SF10) benchmark.

Figure 1 Performance comparison