[MDEV-18483] add extra where unused broke performance Created: 2019-02-05  Updated: 2020-11-11

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

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


 Description   

With this query :

(
 
  SELECT 
    a.`id_mysql_server`, 
    a.`id_ts_variable`, 
    '' as connection_name, 
    a.`date`, 
    a.`value` 
  FROM 
    `ts_value_general_int` a 
    INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
    AND a.date = b.date 
    INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
    AND b.`id_ts_file` = c.`id_ts_file` 
  WHERE 
    id_ts_variable = 588 
    AND a.id_mysql_server IN (
 
      1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
      18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
      28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
      44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
      57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
      69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
      79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
      89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
      99, 100, 101, 102, 103, 104, 105, 106, 
      110, 111
 
    )
 
) 
UNION ALL 
  (
 
    SELECT 
      a.`id_mysql_server`, 
      a.`id_ts_variable`, 
      '' as connection_name, 
      a.`date`, 
      a.`value` 
    FROM 
      `ts_value_general_int` a 
      INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
      AND a.date = b.date 
      INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
      AND b.`id_ts_file` = c.`id_ts_file` 
    WHERE 
      id_ts_variable = 586 
      AND a.id_mysql_server IN (
 
        1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
        18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
        28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
        44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
        57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
        69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
        79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
        89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
        99, 100, 101, 102, 103, 104, 105, 106, 
        110, 111
 
      )
 
  ) 
UNION ALL 
  (
 
    SELECT 
      a.`id_mysql_server`, 
      a.`id_ts_variable`, 
      '' as connection_name, 
      a.`date`, 
      a.`value` 
    FROM 
      `ts_value_general_int` a 
      INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
      AND a.date = b.date 
      INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
      AND b.`id_ts_file` = c.`id_ts_file` 
    WHERE 
      id_ts_variable = 581 
      AND a.id_mysql_server IN (
 
        1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
        18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
        28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
        44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
        57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
        69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
        79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
        89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
        99, 100, 101, 102, 103, 104, 105, 106, 
        110, 111
 
      )
 
  ) 
UNION ALL 
  (
 
    SELECT 
      a.`id_mysql_server`, 
      a.`id_ts_variable`, 
      '' as connection_name, 
      a.`date`, 
      a.`value` 
    FROM 
      `ts_value_general_int` a 
      INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
      AND a.date = b.date 
      INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
      AND b.`id_ts_file` = c.`id_ts_file` 
    WHERE 
      id_ts_variable = 584 
      AND a.id_mysql_server IN (
 
        1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
        18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
        28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
        44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
        57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
        69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
        79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
        89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
        99, 100, 101, 102, 103, 104, 105, 106, 
        110, 111
 
      )
 
  ) 
UNION ALL 
  (
 
    SELECT 
      a.`id_mysql_server`, 
      a.`id_ts_variable`, 
      '' as connection_name, 
      a.`date`, 
      a.`value` 
    FROM 
      `ts_value_general_int` a 
      INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
      AND a.date = b.date 
      INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
      AND b.`id_ts_file` = c.`id_ts_file` 
    WHERE 
      id_ts_variable = 582 
      AND a.id_mysql_server IN (
 
        1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
        18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
        28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
        44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
        57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
        69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
        79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
        89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
        99, 100, 101, 102, 103, 104, 105, 106, 
        110, 111
 
      )
 
  ) 
UNION ALL 
  (
 
    SELECT 
      a.`id_mysql_server`, 
      a.`id_ts_variable`, 
      '' as connection_name, 
      a.`date`, 
      a.`value` 
    FROM 
      `ts_value_general_int` a 
      INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
      AND a.date = b.date 
      INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
      AND b.`id_ts_file` = c.`id_ts_file` 
    WHERE 
      id_ts_variable = 585 
      AND a.id_mysql_server IN (
 
        1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
        18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
        28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
        44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
        57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
        69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
        79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
        89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
        99, 100, 101, 102, 103, 104, 105, 106, 
        110, 111
 
      )
 
  )

to make the thing more simple we will only take one select as :

SELECT 
    a.`id_mysql_server`, 
    a.`id_ts_variable`, 
    '' as connection_name, 
    a.`date`, 
    a.`value` 
  FROM 
    `ts_value_general_int` a 
    INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
    AND a.date = b.date 
    INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
    AND b.`id_ts_file` = c.`id_ts_file` 
  WHERE 
    id_ts_variable = 588 
    AND a.id_mysql_server IN (
 
      1, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 
      18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
      28, 35, 36, 37, 38, 39, 40, 41, 42, 43, 
      44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 
      57, 58, 59, 60, 61, 62, 63, 65, 67, 68, 
      69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 
      79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 
      89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 
      99, 100, 101, 102, 103, 104, 105, 106, 
      110, 111
 
    )

this query run ~1 sec look depending of number of rows in the tables : (the explain)

+------+-------------+-------+-------+------------------------------+-----------------+---------+------------------------------------------------+------+-------------+
 
| id   | select_type | table | type  | possible_keys                | key             | key_len | ref                                            | rows | Extra       |
 
+------+-------------+-------+-------+------------------------------+-----------------+---------+------------------------------------------------+------+-------------+
 
|    1 | SIMPLE      | c     | const | PRIMARY                      | PRIMARY         | 4       | const                                          |    1 |             |
 
|    1 | SIMPLE      | b     | ref   | id_mysql_server_2,id_ts_file | id_ts_file      | 4       | const                                          |   92 | Using where |
 
|    1 | SIMPLE      | a     | ref   | id_mysql_server              | id_mysql_server | 13      | pma_new.b.id_mysql_server,const,pma_new.b.date |    1 |             |
 
+------+-------------+-------+-------+------------------------------+-----------------+---------+------------------------------------------------+------+-------------+
 
3 rows in set (0.919 sec)

for this query :

 SELECT 
  a.`id_mysql_server`, 
  a.`id_ts_variable`, 
  '' as connection_name, 
  a.`date`, 
  a.`value` 
FROM 
  `ts_value_general_int` a 
  INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server 
  AND a.date = b.date 
  INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id 
  AND b.`id_ts_file` = c.`id_ts_file` 
WHERE 
  id_ts_variable = 586;

the result arrive in 0.00 sec

with the same explain except the "Using where" :

 
 
+------+-------------+-------+-------+------------------------------+-----------------+---------+------------------------------------------------+------+-------+
 
| id   | select_type | table | type  | possible_keys                | key             | key_len | ref                                            | rows | Extra |
 
+------+-------------+-------+-------+------------------------------+-----------------+---------+------------------------------------------------+------+-------+
 
|    1 | SIMPLE      | c     | const | PRIMARY                      | PRIMARY         | 4       | const                                          |    1 |       |
 
|    1 | SIMPLE      | b     | ref   | id_mysql_server_2,id_ts_file | id_ts_file      | 4       | const                                          |   92 |       |
 
|    1 | SIMPLE      | a     | ref   | id_mysql_server              | id_mysql_server | 13      | pma_new.b.id_mysql_server,const,pma_new.b.date |    1 |       |
 
+------+-------------+-------+-------+------------------------------+-----------------+---------+------------------------------------------------+------+-------+
 
3 rows in set (0.000 sec)



 Comments   
Comment by Aurélien LEQUOY [ 2019-02-05 ]

Creates table :

CREATE TABLE `ts_value_general_int` (
 
  `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` bigint(20) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`id`,`date`),
 
  KEY `id_mysql_server` (`id_mysql_server`,`id_ts_variable`,`date`)
 
) ENGINE=ROCKSDB AUTO_INCREMENT=31556580281 DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (to_days(`date`))
 
(PARTITION `p737435` VALUES LESS THAN (737435) ENGINE = ROCKSDB,
 
 PARTITION `p737436` VALUES LESS THAN (737436) ENGINE = ROCKSDB,
 
 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);
 
 
 
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=2177 DEFAULT CHARSET=latin1;
 
 
 
 
 
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=745 DEFAULT CHARSET=latin1
 
 

Comment by Aurélien LEQUOY [ 2019-02-05 ]

I making a small example with 3 000 000 000 of rows because with less we cannot see difference.
This table is used to stored all status of MySQL server and grow really fast until ~200 000 000 000 of rows.

The id_mysql_server is specified to filter servers. Was working really fine before I changed some (Engine / index / PK) this example make me crazy.
Look like the id_mysql_server is filtered in first, and he should be filtered in last.

Actually it's more faster to ask ALL, and filter on PHP xD, or have to make a CTE or subselect to filter but kinda uggly.

Comment by Aurélien LEQUOY [ 2019-02-07 ]

I upload a test case ~20GB

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