[MDEV-19013] Possible performance problem, query with CTE and IN(subquery) vs = (subquery) Created: 2019-03-21  Updated: 2021-01-14

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.3.13
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Aurélien LEQUOY Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu 18.04


 Description   

I asked myself why my query well coded was made so long time !

I put with CTE but without CTE the problem is the same !

first query : 

WITH `z` as (select `id` from `ts_variable` where `name` = 'version' and `from` = 'variables')
 
SELECT `a`.`id_mysql_server` FROM `ts_max_date` `a`
 
INNER JOIN `ts_file` `b` ON `a`.`id_ts_file` = `b`.`id`
 
LEFT JOIN `ts_value_general_text` d ON d.date = a.date AND a.id_mysql_server = d.id_mysql_server AND d.id_ts_variable  IN (SELECT id from z)
 
WHERE d.id is null AND b.file_name = "variable";
 
 
 
no answer after 2 minutes ...
 
 
 
+------+--------------+-------------+-------+-------------------+-----------------+---------+---------------------------+------+----------+--------------------------------------+
 
| id   | select_type  | table       | type  | possible_keys     | key             | key_len | ref                       | rows | filtered | Extra                                |
 
+------+--------------+-------------+-------+-------------------+-----------------+---------+---------------------------+------+----------+--------------------------------------+
 
|    1 | PRIMARY      | b           | const | PRIMARY,file_name | file_name       | 52      | const                     |    1 |   100.00 | Using index                          |
 
|    1 | PRIMARY      | a           | ref   | id_ts_file        | id_ts_file      | 4       | const                     |   93 |   100.00 |                                      |
 
|    1 | PRIMARY      | d           | ref   | id_mysql_server   | id_mysql_server | 4       | pma_new.a.id_mysql_server | 5354 |   100.00 | Using where; Using index; Not exists |
 
|    3 | MATERIALIZED | ts_variable | const | name              | name            | 132     | const,const               |    1 |   100.00 | Using index                          |
 
+------+--------------+-------------+-------+-------------------+-----------------+---------+---------------------------+------+----------+--------------------------------------+
 
4 rows in set, 1 warning (0.002 sec)

i replaced IN by =

WITH `z` as (select `id` from `ts_variable` where `name` = 'version')
 
SELECT `a`.`id_mysql_server` FROM `ts_max_date` `a`
 
INNER JOIN `ts_file` `b` ON `a`.`id_ts_file` = `b`.`id`
 
LEFT JOIN `ts_value_general_text` c ON c.date = a.date AND a.id_mysql_server = c.id_mysql_server AND c.id_ts_variable = (SELECT id from z)
 
WHERE b.file_name = "variable" and  c.id is null ;
 
 
 
+-----------------+
 
| id_mysql_server |
 
+-----------------+
 
|              18 |
 
|              19 |
 
|              20 |
 
|              21 |
 
|              22 |
 
|              23 |
 
|              24 |
 
|              25 |
 
|              42 |
 
|              56 |
 
|              62 |
 
|              63 |
 
|              96 |
 
|              99 |
 
|             100 |
 
+-----------------+
 
15 rows in set (0.002 sec)
 
 
 
+------+-------------+-------------+-------+-------------------+-----------------+---------+------------------------------------------------+------+----------+--------------------------------------+
 
| id   | select_type | table       | type  | possible_keys     | key             | key_len | ref                                            | rows | filtered | Extra                                |
 
+------+-------------+-------------+-------+-------------------+-----------------+---------+------------------------------------------------+------+----------+--------------------------------------+
 
|    1 | PRIMARY     | b           | const | PRIMARY,file_name | file_name       | 52      | const                                          |    1 |   100.00 | Using index                          |
 
|    1 | PRIMARY     | a           | ref   | id_ts_file        | id_ts_file      | 4       | const                                          |   93 |   100.00 |                                      |
 
|    1 | PRIMARY     | c           | ref   | id_mysql_server   | id_mysql_server | 13      | pma_new.a.id_mysql_server,const,pma_new.a.date |    1 |   100.00 | Using where; Using index; Not exists |
 
|    3 | SUBQUERY    | ts_variable | ref   | name              | name            | 66      | const                                          |    1 |   100.00 | Using where; Using index             |
 
+------+-------------+-------------+-------+-------------------+-----------------+---------+------------------------------------------------+------+----------+--------------------------------------+
 
4 rows in set, 1 warning (0.002 sec)

table definition :

CREATE TABLE `ts_variable` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `id_ts_file` int(11) NOT NULL DEFAULT 3,
 
  `name` varchar(64) NOT NULL,
 
  `type` enum('INT','DOUBLE','TEXT','') NOT NULL COMMENT '1 => int, 2 => double, 3 => text',
 
  `from` varchar(64) NOT NULL,
 
  `radical` char(10) NOT NULL,
 
  `is_derived` int(11) DEFAULT 1,
 
  `is_dynamic` int(11) DEFAULT 1 COMMENT 'only for global variables',
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `name` (`name`,`from`)
 
) ENGINE=ROCKSDB AUTO_INCREMENT=2178 DEFAULT CHARSET=latin1
 
1 row in set (0.000 sec)
 
 
 
CREATE TABLE `ts_max_date` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `id_daemon_main` int(11) NOT NULL,
 
  `id_mysql_server` int(11) NOT NULL,
 
  `date` datetime NOT NULL,
 
  `date_p1` datetime NOT NULL,
 
  `date_p2` datetime NOT NULL,
 
  `date_p3` datetime NOT NULL,
 
  `date_p4` datetime NOT NULL,
 
  `id_ts_file` int(11) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `id_mysql_server_2` (`id_mysql_server`,`id_ts_file`),
 
  UNIQUE KEY `id_mysql_server` (`id_mysql_server`,`id_ts_file`,`date`),
 
  UNIQUE KEY `date` (`date`,`id_ts_file`,`id_mysql_server`),
 
  KEY `id_ts_file` (`id_ts_file`),
 
  CONSTRAINT `ts_max_date_ibfk_2` FOREIGN KEY (`id_mysql_server`) REFERENCES `mysql_server` (`id`) ON DELETE CASCADE,
 
  CONSTRAINT `ts_max_date_ibfk_3` FOREIGN KEY (`id_ts_file`) REFERENCES `ts_file` (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=749 DEFAULT CHARSET=latin1
 
1 row in set (0.000 sec)
 
 
 
 CREATE TABLE `ts_file` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `file_name` varchar(50) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `file_name` (`file_name`)
 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
 
1 row in set (0.000 sec)
 
 
 
CREATE TABLE `ts_value_general_text` (
 
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `id_mysql_server` int(11) NOT NULL,
 
  `id_ts_variable` int(11) NOT NULL,
 
  `date` datetime NOT NULL,
 
  `value` text NOT NULL,
 
  PRIMARY KEY (`id`,`date`),
 
  KEY `id_mysql_server` (`id_mysql_server`,`id_ts_variable`,`date`)
 
) ENGINE=ROCKSDB AUTO_INCREMENT=20278797868 DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (to_days(`date`))
 
(PARTITION `p737437` VALUES LESS THAN (737437) ENGINE = ROCKSDB,
 
 PARTITION `p737438` VALUES LESS THAN (737438) ENGINE = ROCKSDB,
 
 PARTITION `p737439` VALUES LESS THAN (737439) ENGINE = ROCKSDB,
 
 PARTITION `p737440` VALUES LESS THAN (737440) ENGINE = ROCKSDB,
 
 PARTITION `p737441` VALUES LESS THAN (737441) ENGINE = ROCKSDB,
 
 PARTITION `p737442` VALUES LESS THAN (737442) ENGINE = ROCKSDB,
 
 PARTITION `p737443` VALUES LESS THAN (737443) ENGINE = ROCKSDB,
 
 PARTITION `p737444` VALUES LESS THAN (737444) ENGINE = ROCKSDB,
 
 PARTITION `p737445` VALUES LESS THAN (737445) ENGINE = ROCKSDB,
 
 PARTITION `p737446` VALUES LESS THAN (737446) ENGINE = ROCKSDB,
 
 PARTITION `p737447` VALUES LESS THAN (737447) ENGINE = ROCKSDB,
 
 PARTITION `p737453` VALUES LESS THAN (737453) ENGINE = ROCKSDB,
 
 PARTITION `p737454` VALUES LESS THAN (737454) ENGINE = ROCKSDB,
 
 PARTITION `p737455` VALUES LESS THAN (737455) ENGINE = ROCKSDB,
 
 PARTITION `p737456` VALUES LESS THAN (737456) ENGINE = ROCKSDB,
 
 PARTITION `p737457` VALUES LESS THAN (737457) ENGINE = ROCKSDB,
 
 PARTITION `p737458` VALUES LESS THAN (737458) ENGINE = ROCKSDB,
 
 PARTITION `p737459` VALUES LESS THAN (737459) ENGINE = ROCKSDB,
 
 PARTITION `p737460` VALUES LESS THAN (737460) ENGINE = ROCKSDB,
 
 PARTITION `p737461` VALUES LESS THAN (737461) ENGINE = ROCKSDB,
 
 PARTITION `p737462` VALUES LESS THAN (737462) ENGINE = ROCKSDB,
 
 PARTITION `p737463` VALUES LESS THAN (737463) ENGINE = ROCKSDB,
 
 PARTITION `p737464` VALUES LESS THAN (737464) ENGINE = ROCKSDB,
 
 PARTITION `p737465` VALUES LESS THAN (737465) ENGINE = ROCKSDB,
 
 PARTITION `p737466` VALUES LESS THAN (737466) ENGINE = ROCKSDB,
 
 PARTITION `p737467` VALUES LESS THAN (737467) ENGINE = ROCKSDB,
 
 PARTITION `p737468` VALUES LESS THAN (737468) ENGINE = ROCKSDB,
 
 PARTITION `p737469` VALUES LESS THAN (737469) ENGINE = ROCKSDB,
 
 PARTITION `p737470` VALUES LESS THAN (737470) ENGINE = ROCKSDB,
 
 PARTITION `p737471` VALUES LESS THAN (737471) ENGINE = ROCKSDB,
 
 PARTITION `p737472` VALUES LESS THAN (737472) ENGINE = ROCKSDB,
 
 PARTITION `p737473` VALUES LESS THAN (737473) ENGINE = ROCKSDB,
 
 PARTITION `p737474` VALUES LESS THAN (737474) ENGINE = ROCKSDB,
 
 PARTITION `p737475` VALUES LESS THAN (737475) ENGINE = ROCKSDB,
 
 PARTITION `p737481` VALUES LESS THAN (737481) ENGINE = ROCKSDB,
 
 PARTITION `p737482` VALUES LESS THAN (737482) ENGINE = ROCKSDB,
 
 PARTITION `p737483` VALUES LESS THAN (737483) ENGINE = ROCKSDB,
 
 PARTITION `p737484` VALUES LESS THAN (737484) ENGINE = ROCKSDB,
 
 PARTITION `p737485` VALUES LESS THAN (737485) ENGINE = ROCKSDB,
 
 PARTITION `p737486` VALUES LESS THAN (737486) ENGINE = ROCKSDB,
 
 PARTITION `p737488` VALUES LESS THAN (737488) ENGINE = ROCKSDB,
 
 PARTITION `p737489` VALUES LESS THAN (737489) ENGINE = ROCKSDB,
 
 PARTITION `p737490` VALUES LESS THAN (737490) ENGINE = ROCKSDB,
 
 PARTITION `p737491` VALUES LESS THAN (737491) ENGINE = ROCKSDB,
 
 PARTITION `p737492` VALUES LESS THAN (737492) ENGINE = ROCKSDB,
 
 PARTITION `p737493` VALUES LESS THAN (737493) ENGINE = ROCKSDB,
 
 PARTITION `p737494` VALUES LESS THAN (737494) ENGINE = ROCKSDB,
 
 PARTITION `p737495` VALUES LESS THAN (737495) ENGINE = ROCKSDB,
 
 PARTITION `p737496` VALUES LESS THAN (737496) ENGINE = ROCKSDB,
 
 PARTITION `p737502` VALUES LESS THAN (737502) ENGINE = ROCKSDB,
 
 PARTITION `p737503` VALUES LESS THAN (737503) ENGINE = ROCKSDB,
 
 PARTITION `p737504` VALUES LESS THAN (737504) ENGINE = ROCKSDB)
 
1 row in set (0.001 sec)



 Comments   
Comment by Aurélien LEQUOY [ 2019-03-22 ]

maybe because table `ts_value_general_text` is on LEFT JOIN ?

i join 200 serveurs to get their last data about (SHOW GLOBAL VARIABLES) and I wanna know in BRUT table ts_value_general_text, if there is still brut datas for this date.

Comment by Aurélien LEQUOY [ 2019-03-22 ]

you can remove is null if you want the problem is the same :

 
 
MariaDB [pmacontrol]> WITH `z` as (select `id` from `ts_variable` where `name` = 'version')  SELECT `a`.`id_mysql_server` FROM `ts_max_date` `a`  INNER JOIN `ts_file` `b` ON `a`.`id_ts_file` = `b`.`id`  LEFT JOIN `ts_value_general_text` c ON c.date = a.date AND a.id_mysql_server = c.id_mysql_server AND c.id_ts_variable = (SELECT id from z)  WHERE b.file_name = "variable";
 
+-----------------+
 
| id_mysql_server |
 
+-----------------+
 
|               1 |
 
|               2 |
 
|               3 |
 
+-----------------+
 
3 rows in set (0.003 sec)
 
 
 
 
 
MariaDB [pmacontrol]> WITH `z` as (select `id` from `ts_variable` where `name` = 'version')  SELECT `a`.`id_mysql_server` FROM `ts_max_date` `a`  INNER JOIN `ts_file` `b` ON `a`.`id_ts_file` = `b`.`id`  LEFT JOIN `ts_value_general_text` c ON c.date = a.date AND a.id_mysql_server = c.id_mysql_server AND c.id_ts_variable in (SELECT id from z)  WHERE b.file_name = "variable";
 
+-----------------+
 
| id_mysql_server |
 
+-----------------+
 
|               1 |
 
|               2 |
 
|               3 |
 
+-----------------+
 
3 rows in set (10.386 sec)

On this example ts_value_general_text have only 9 202 604 of lines

Comment by Aurélien LEQUOY [ 2019-03-22 ]

maybe more understandle :

MariaDB [pmacontrol]> WITH `z` as (select `id` from `ts_variable` where `name` = 'version')  SELECT `a`.`id_mysql_server`,c.id FROM `ts_max_date` `a`  INNER JOIN `ts_file` `b` ON `a`.`id_ts_file` = `b`.`id`  LEFT JOIN `ts_value_general_text` c ON c.date = a.date AND a.id_mysql_server = c.id_mysql_server AND c.id_ts_variable in (SELECT id from z)  WHERE b.file_name = "variable";
 
+-----------------+---------+
 
| id_mysql_server | id      |
 
+-----------------+---------+
 
|               1 |    NULL |
 
|               2 | 9111065 |
 
|               3 | 8170580 |
 
+-----------------+---------+
 
3 rows in set (9.496 sec)

Comment by Alice Sherepa [ 2020-09-11 ]

Sorry for the long delay in the answer. Is this issue still relevant for you?

Comment by Aurélien LEQUOY [ 2021-01-14 ]

problem still there on 10.4.17

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