Details
Description
I noticed a significant slowdown in a specific query following the update to MariaDB 10.6.12 (unaware of the previous version). It appears that instead of utilizing an index, the query now scans the entire table, leading to the decreased performance:
MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed');
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t | index | NULL | PRIMARY | 4 | NULL | 44100 | Using index |
|
| 1 | SIMPLE | open | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1 | test.t.id | 1 (0%) | Using where; Using rowid filter |
|
| 1 | SIMPLE | closed | ALL | PRIMARY,status | NULL | NULL | NULL | 44100 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+-------------------------------------------------+
|
3 rows in set (0.001 sec)
|
However, when I simply switch around 'open' and 'closed' the issue goes away:
MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='closed') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='open');
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+---------------------------------+
|
| 1 | SIMPLE | t | index | NULL | PRIMARY | 4 | NULL | 44100 | Using index |
|
| 1 | SIMPLE | open | eq_ref | PRIMARY,status | PRIMARY | 4 | test.t.id | 1 | Using where |
|
| 1 | SIMPLE | closed | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1 | test.t.id | 1 (0%) | Using where; Using rowid filter |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+---------------------------------+
|
3 rows in set (0.001 sec)
|
I checked the 10.6.14, 10.9, and 10 docker containers, and they all have this problem. However, it appears to be fixed in version 11/latest.
To perform a basic test, please refer to the queries mentioned above. I have included a table dump for your reference.
There also appears to be a specific threshold for this issue. In my actual data, it only occurred after approximately 40,000 entries in the table. However, in the minimal test table (where I removed all columns except for "status"), the issue appeared after 97 entries. Perhaps it is somehow related to the overall size of the table:
MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed');
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t | index | NULL | PRIMARY | 4 | NULL | 97 | Using index |
|
| 1 | SIMPLE | open | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1 | test.t.id | 1 (1%) | Using where; Using rowid filter |
|
| 1 | SIMPLE | closed | ALL | PRIMARY,status | NULL | NULL | NULL | 97 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+-------------------------------------------------+
|
3 rows in set (0.001 sec)
|
|
|
MariaDB [test]> delete from test limit 1;
|
Query OK, 1 row affected (0.002 sec)
|
|
|
MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed');
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+---------------------------------+
|
| 1 | SIMPLE | t | index | NULL | PRIMARY | 4 | NULL | 96 | Using index |
|
| 1 | SIMPLE | open | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1 | test.t.id | 1 (1%) | Using where; Using rowid filter |
|
| 1 | SIMPLE | closed | eq_ref | PRIMARY,status | PRIMARY | 4 | test.t.id | 1 | Using where |
|
+------+-------------+--------+---------------+----------------+----------------+---------+-----------+--------+---------------------------------+
|
3 rows in set (0.001 sec)
|
Attachments
Issue Links
- relates to
-
MDEV-30566 Performance Degraded After Migration From 10.2.25 To 10.5.18
-
- Open
-