Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24849

high optimizer_search_depth affects query performance

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.4
    • 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

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              muhammad.irfan Muhammad Irfan
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration