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

Performance Tests

  • Run following SQL statement (Q1) with no predicate conditions to access the primary table.
    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)
    ​
  • Run following SQL statement (Q2) with predicate conditions to access the secondary index (including the index range conditions). Information about other columns needs to be obtained from the table.
    mysql> 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 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 benchmark (10 scale).

Figure 1 Performance comparison