[MDEV-24849] high optimizer_search_depth affects query performance - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.4(EOL)
Fix Version/s: N/A
Component/s: Optimizer
Labels:
None

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                              |

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

Attachments

Issue Links

relates to

MDEV-19532 Query with subqueries hangs for long time with default optimizer_search_depth

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(3 mentioned in)

Activity

People

Assignee:: Sergei Golubchik

Reporter:: Muhammad Irfan

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2021-02-11 17:03

Updated:: 2024-09-06 10:00

Resolved:: 2021-02-12 12:27

Git Integration

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