Help Center/ GaussDB(for MySQL)/ Kernels/ Common Kernel Functions/ LIMIT...OFFSET Pushdown/ Performance Tests
Updated on 2024-09-06 GMT+08:00
View PDF
Share

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

Parent topic: LIMIT...OFFSET Pushdown