[MDEV-38503] Exponential query plan when using rowid_filter with many subqueries - Jira

XML

Word

Printable

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.4, 11.8, 12.2, 11.8.5
Fix Version/s: 11.4, 11.8, 12.2
Component/s: Optimizer
Labels:
- rowid_filtering

Description

Our application generates automated queries that are very much not optimal; however performance has usually been acceptable. Today we ran into a query that was spending hours in the query planning stage, after some playing around with optimizer switches, the rowid_filter seems to be causing exponential planning time when used with many subqueries. The below output shows an example query with 14 then 16 subqueries, jumping from 2 seconds to 32 seconds with the default optimizer settings, and completing in < 1 second with rowid_filter off.

MariaDB [liquipediadb]> explain SELECT * FROM `wikiapex_lpdb_match2`  WHERE (( `date` < '2026-01-08 00:00:00.000000' )  AND ( `pageid` = '53347' )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Gen.G Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Shopify Rebellion') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'S8UL Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'ROC Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Fnatic') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'FYR Strays') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Hotdog Mafia') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'REIGNITE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'SYRALE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Men Of Culture') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Falcons') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Liquid') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Five Fears') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'UNLIMIT') ) ) AND `namespace` = 0 LIMIT 20;

+------+-------------+------------------------------+------------+--------------------------------+---------------+---------+--------------------------------------------+---------+---------------------------------------------------------------------------+

| id   | select_type | table                        | type       | possible_keys                  | key           | key_len | ref                                        | rows    | Extra                                                                     |

+------+-------------+------------------------------+------------+--------------------------------+---------------+---------+--------------------------------------------+---------+---------------------------------------------------------------------------+

|    1 | PRIMARY     | wikiapex_lpdb_match2         | ref        | pageid,match2id,namespace,date | pageid        | 4       | const                                      | 6       | Using where                                                               |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2); Using rowid filter         |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref        | match2id,name                  | match2id      | 257     | liquipediadb.wikiapex_lpdb_match2.match2id | 19      | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                     |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref        | match2id,name                  | match2id      | 257     | liquipediadb.wikiapex_lpdb_match2.match2id | 19      | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                     |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref        | match2id,name                  | match2id      | 257     | liquipediadb.wikiapex_lpdb_match2.match2id | 19      | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                     |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

+------+-------------+------------------------------+------------+--------------------------------+---------------+---------+--------------------------------------------+---------+---------------------------------------------------------------------------+

15 rows in set (2.722 sec)

MariaDB [liquipediadb]> explain SELECT * FROM `wikiapex_lpdb_match2`  WHERE (( `date` < '2026-01-08 00:00:00.000000' )  AND ( `pageid` = '53347' )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Gen.G Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Shopify Rebellion') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'S8UL Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'ROC Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Fnatic') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'FYR Strays') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Hotdog Mafia') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'REIGNITE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'SYRALE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Men Of Culture') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Falcons') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Liquid') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Five Fears') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'UNLIMIT') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Al Qadsiah') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Virtus.pro') )   ) AND `namespace` = 0 LIMIT 20;

+------+-------------+------------------------------+------------+--------------------------------+---------------+---------+--------------------------------------------+---------+---------------------------------------------------------------------------+

| id   | select_type | table                        | type       | possible_keys                  | key           | key_len | ref                                        | rows    | Extra                                                                     |

+------+-------------+------------------------------+------------+--------------------------------+---------------+---------+--------------------------------------------+---------+---------------------------------------------------------------------------+

|    1 | PRIMARY     | wikiapex_lpdb_match2         | ref        | pageid,match2id,namespace,date | pageid        | 4       | const                                      | 6       | Using where                                                               |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2); Using rowid filter         |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref        | match2id,name                  | match2id      | 257     | liquipediadb.wikiapex_lpdb_match2.match2id | 19      | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                     |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref        | match2id,name                  | match2id      | 257     | liquipediadb.wikiapex_lpdb_match2.match2id | 19      | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                     |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref        | match2id,name                  | match2id      | 257     | liquipediadb.wikiapex_lpdb_match2.match2id | 19      | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                     |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref|filter | match2id,name                  | match2id|name | 257|257 | liquipediadb.wikiapex_lpdb_match2.match2id | 19 (0%) | Using where; FirstMatch(wikiapex_lpdb_match2opponent); Using rowid filter |

+------+-------------+------------------------------+------------+--------------------------------+---------------+---------+--------------------------------------------+---------+---------------------------------------------------------------------------+

17 rows in set (32.383 sec)

MariaDB [liquipediadb]> set optimizer_switch='rowid_filter=off';

Query OK, 0 rows affected (0.001 sec)

MariaDB [liquipediadb]> explain SELECT * FROM `wikiapex_lpdb_match2`  WHERE (( `date` < '2026-01-08 00:00:00.000000' )  AND ( `pageid` = '53347' )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Gen.G Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Shopify Rebellion') )

 AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'S8UL Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'ROC Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Fnatic') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'FYR Strays') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Hotdog Mafia') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'REIGNITE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'SYRALE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Men Of Culture') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Falcons') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Liquid') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Five Fears') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'UNLIMIT') ) ) AND `namespace` = 0 LIMIT 20;

+------+-------------+------------------------------+-------+--------------------------------+----------+---------+----------------------------------------------------+------+---------------------------------------------------------------------------------------+

| id   | select_type | table                        | type  | possible_keys                  | key      | key_len | ref                                                | rows | Extra                                                                                 |

+------+-------------+------------------------------+-------+--------------------------------+----------+---------+----------------------------------------------------+------+---------------------------------------------------------------------------------------+

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Start temporary                                                |

|    1 | PRIMARY     | wikiapex_lpdb_match2         | range | pageid,match2id,namespace,date | pageid   | 4       | NULL                                               | 6    | Using index condition; Using where; End temporary; Using join buffer (flat, BNL join) |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2)                                         |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Using where; FirstMatch(wikiapex_lpdb_match2opponent)          |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Using where; FirstMatch(wikiapex_lpdb_match2opponent)          |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Using where; FirstMatch(wikiapex_lpdb_match2opponent)          |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

+------+-------------+------------------------------+-------+--------------------------------+----------+---------+----------------------------------------------------+------+---------------------------------------------------------------------------------------+

15 rows in set (0.060 sec)

MariaDB [liquipediadb]> explain SELECT * FROM `wikiapex_lpdb_match2`  WHERE (( `date` < '2026-01-08 00:00:00.000000' )  AND ( `pageid` = '53347' )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Gen.G Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Shopify Rebellion') )

 AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'S8UL Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'ROC Esports') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Fnatic') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'FYR Strays') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Hotdog Mafia') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'REIGNITE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'SYRALE') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Men Of Culture') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Falcons') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Team Liquid') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Five Fears') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'UNLIMIT') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Al Qadsiah') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'Virtus.pro') )  AND ( `match2id` IN (SELECT match2id FROM `wikiapex_lpdb_match2opponent` WHERE name = 'TSM') )  ) AND `namespace` = 0 LIMIT 20;

+------+-------------+------------------------------+-------+--------------------------------+----------+---------+----------------------------------------------------+------+---------------------------------------------------------------------------------------+

| id   | select_type | table                        | type  | possible_keys                  | key      | key_len | ref                                                | rows | Extra                                                                                 |

+------+-------------+------------------------------+-------+--------------------------------+----------+---------+----------------------------------------------------+------+---------------------------------------------------------------------------------------+

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Start temporary                                                |

|    1 | PRIMARY     | wikiapex_lpdb_match2         | range | pageid,match2id,namespace,date | pageid   | 4       | NULL                                               | 6    | Using index condition; Using where; End temporary; Using join buffer (flat, BNL join) |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2)                                         |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Using where; FirstMatch(wikiapex_lpdb_match2opponent)          |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Using where; FirstMatch(wikiapex_lpdb_match2opponent)          |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | name     | 257     | const                                              | 13   | Using index condition; Using where; FirstMatch(wikiapex_lpdb_match2opponent)          |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

|    1 | PRIMARY     | wikiapex_lpdb_match2opponent | ref   | match2id,name                  | match2id | 257     | liquipediadb.wikiapex_lpdb_match2opponent.match2id | 19   | Using where; FirstMatch(wikiapex_lpdb_match2opponent)                                 |

+------+-------------+------------------------------+-------+--------------------------------+----------+---------+----------------------------------------------------+------+---------------------------------------------------------------------------------------+

18 rows in set (0.368 sec)

MariaDB [liquipediadb]> SELECT COUNT(*) FROM wikiapex_lpdb_match2opponent;

+----------+

| COUNT(*) |

+----------+

|   112283 |

+----------+

I'm very aware that this query is bad and could be written much simpler and more efficient, however it's automatically generated by our application logic that concatenates the various conditions like this without regard for efficiency. Even though it's a bad query, I would not expect it to spend hours in the planning stage.

I captured a partial optimizer trace during the issue, hopefully it is enough to demonstrate the problem. If not please let me know and I can re-run the scenario with a larger buffer.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

optimizer_trace.txt
1.02 MB
2026-01-07 20:16

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Richard Stanway

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2026-01-07 20:32

Updated:: 2026-01-08 12:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.