Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5
Description
Optimizer chooses bad strategy for some word press queries on table wp_postmeta:
SELECT post_id, meta_key, meta_value |
FROM wp_postmeta |
WHERE post_id IN (27529,...1125 other entries...,10584) |
ORDER BY meta_id ASC |
;
|
- Up to MariaDB 10.2
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
|
| 1 | SIMPLE | wp_postmeta | range | post_id | post_id | 8 | NULL | 25301 | Using index condition; Using filesort |
|
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+---------------------------------------+
|
Execution time about: 100 - 110 ms.
- From MariaDB 10.3
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1128 | Using temporary; Using filesort |
|
| 1 | PRIMARY | wp_postmeta | ref | post_id | post_id | 8 | tvc_0._col_1 | 4 | Using index condition |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1128 | |
|
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+--------------+-------------+------+---------------+---------+---------+--------------+------+---------------------------------+
|
Execution time: About 150 ms (50% worse).
We did not find any way to force the optimizer to choose the older execution plan with optimizer_switches:
set session optimizer_switch='materialization=off,semijoin=off';
set session optimizer_switch='derived_merge=OFF';
https://mariadb.org/wp-content/uploads/2018/07/state-of-mariadb-optimizer-r08.pdf
https://mariadb.com/kb/en/derived-table-with-key-optimization/
https://mariadb.com/kb/en/semi-join-materialization-strategy/
https://mariadb.com/kb/en/derived-table-merge-optimization/
Further a Execution Plan of a competitive product did slightly better:
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
|
| 1 | SIMPLE | wp_postmeta | NULL | range | post_id | post_id | 8 | NULL | 6768 | 100.00 | Using index condition; Using MRR; Using filesort |
|
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+
|
Execution time: 80 ms (100% improvement to the previous).
We were not able to enforce this better Execution plan with optimizer_switches either:
set session optimizer_switch='mrr=on,mrr_sort_keys=on';
(psergey: edited to make more readable)
