Updated on 2024-09-06 GMT+08:00

Usage

Table 1 Parameter description

Parameter

Level

Description

optimizer_switch

Global, Session

Enables or disables query optimization.

After this parameter is enabled, you can configure offset_pushdown to enable or disable LIMIT OFFSET pushdown.

  • ON: enabled
  • OFF: (default value): disabled

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)