[MDEV-24849] high optimizer_search_depth affects query performance Created: 2021-02-11  Updated: 2022-04-13  Resolved: 2021-02-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Muhammad Irfan Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-19532 Query with subqueries hangs for long ... Closed

 Description   

I found query performance impacted with high no. of EXISTS clause condition and query never returns. With that many conditions, optimizer spends ages on building the plan.

mysql [localhost:10412] {root} (test) > CREATE TABLE `item` (
 
    ->  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 
    ->  `title` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
 
    ->  PRIMARY KEY (`id`)
 
    -> ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.015 sec)
 
 
 
mysql [localhost:10412] {root} (test) > INSERT INTO `item` (`id`,`title`) VALUES (1,'A'), (2,'B');
 
Query OK, 2 rows affected (0.002 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
mysql [localhost:10412] {root} (test) > 
mysql [localhost:10412] {root} (test) > CREATE TABLE `item_tag` (
 
    ->  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 
    ->  `item_id` INT(11) UNSIGNED NOT NULL,
 
    ->  `tag_id` INT(11) UNSIGNED NOT NULL,
 
    ->  PRIMARY KEY (`id`),
 
    ->  UNIQUE INDEX `item_id_tag_id` (`item_id`, `tag_id`),
 
    ->  INDEX `item_id` (`item_id`),
 
    ->  INDEX `tag_id` (`tag_id`)
 
    -> ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.014 sec)
 
 
 
mysql [localhost:10412] {root} (test) > INSERT INTO `item_tag` (`item_id`,`tag_id`) VALUES (1,1), (1,2);
 
Query OK, 2 rows affected (0.005 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
mysql [localhost:10412] {root} (test) > EXPLAIN SELECT 
    ->  i.*
 
    -> FROM `item` i
 
    -> WHERE EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`);
 
 
 
mysql [localhost:10412] {root} (test) > SELECT 
    ->  i.*
 
    -> FROM `item` i
 
    -> WHERE EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`);
 
^CCtrl-C -- query killed. Continuing normally.
 
ERROR 1317 (70100): Query execution was interrupted

This query never returns. It stays in "statistics" state. 

*************************** 26. row ***************************
 
      Id: 33
 
    User: root
 
    Host: localhost
 
      db: test
 
 Command: Query
 
    Time: 34
 
   State: Statistics
 
    Info: SELECT 
 i.*
 
FROM `item` i

With small no. of conditions with EXISTS clause it is slow but query completes. After experimenting with optimizer_search_depth It works with low value of search depth.

mysql [localhost:10412] {root} (test) > set session optimizer_search_depth = 1; 
Query OK, 0 rows affected (0.000 sec)
 
 
 
mysql [localhost:10412] {root} (test) > EXPLAIN SELECT 
    ->  i.*
 
    -> FROM `item` i
 
    -> WHERE EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`);
 
+------+-------------+----------+--------+-------------------------------+----------------+---------+-----------------+------+------------------------------------------------+
 
| id   | select_type | table    | type   | possible_keys                 | key            | key_len | ref             | rows | Extra                                          |
 
+------+-------------+----------+--------+-------------------------------+----------------+---------+-----------------+------+------------------------------------------------+
 
|    1 | PRIMARY     | i        | ALL    | PRIMARY                       | NULL           | NULL    | NULL            | 2    |                                                |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(i)        |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | ref    | item_id_tag_id,item_id,tag_id | item_id_tag_id | 4       | test.i.id       | 1    | Using where; Using index; FirstMatch(item_tag) |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
|    1 | PRIMARY     | item_tag | eq_ref | item_id_tag_id,item_id,tag_id | item_id_tag_id | 8       | test.i.id,const | 1    | Using index                                    |
 
+------+-------------+----------+--------+-------------------------------+----------------+---------+-----------------+------+------------------------------------------------+
 
34 rows in set (0.005 sec)
 
 
 
mysql [localhost:10412] {root} (test) > SELECT 
    ->  i.*
 
    -> FROM `item` i
 
    -> WHERE EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`)
 
    -> AND EXISTS( SELECT '' FROM `item_tag` WHERE `tag_id` IN(1,2) AND `item_id` = i.`id`);
 
+----+-------+
 
| id | title |
 
+----+-------+
 
|  1 | A     |
 
+----+-------+
 
1 row in set (0.006 sec)
 
 
 
mysql [localhost:10412] {root} (test) > SHOW GLOBAL VARIABLES LIKE '%version%';
 
+-----------------------------------+------------------------------------------+
 
| Variable_name                     | Value                                    |
 
+-----------------------------------+------------------------------------------+
 
| in_predicate_conversion_threshold | 1000                                     |
 
| innodb_version                    | 10.4.12                                  |
 
| protocol_version                  | 10                                       |
 
| slave_type_conversions            |                                          |
 
| spider_version                    | 3.3.15                                   |
 
| system_versioning_alter_history   | ERROR                                    |
 
| system_versioning_asof            | DEFAULT                                  |
 
| tls_version                       | TLSv1.1,TLSv1.2,TLSv1.3                  |
 
| version                           | 10.4.12-MariaDB                          |
 
| version_comment                   | MariaDB Server                           |
 
| version_compile_machine           | x86_64                                   |
 
| version_compile_os                | Linux                                    |
 
| version_malloc_library            | system                                   |
 
| version_source_revision           | ba6bfc402c352372cc1a9ec20b5dc50b2204549f |
 
| version_ssl_library               | WolfSSL 4.3.0                            |
 
| wsrep_patch_version               | wsrep_26.22                              |
 
+-----------------------------------+------------------------------------------+



 Comments   
Comment by Sergei Golubchik [ 2021-02-12 ]

The variable documentation says

Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value.

If that's not enough, see Igor's explanation in the linked issue

Comment by Valerii Kravchuk [ 2021-02-12 ]

So, the default value, 62, is not "safe" for complex queries. Maybe we have to think about a different, smaller default value then?

Generated at Thu Feb 08 09:33:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.