Usage

You can also add a HINT clause to enable or disable LIMIT OFFSET pushdown.

• OFFSET_PUSHDOWN(table_name): enabled
• NO_OFFSET_PUSHDOWN(table_name): disabled

Example:

Take a schema as an example in a TPC-H test. After LIMIT OFFSET pushdown is enabled using the parameter or the HINT clause, Using limit-offset pushdown is displayed in the Extra column when you run EXPLAIN SQL to view an execution plan.

• Enabling LIMIT OFFSET pushdown by configuring offset_pushdown

  mysql> 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 by adding a HINT clause

  mysql> 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)
  ​
  mysql> 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)