Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.13
-
None
-
None
-
None
-
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)
|