[MDEV-18483] add extra where unused broke performance - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: None
Fix Version/s: None
Component/s: Optimizer
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)

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Aurélien LEQUOY

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2019-02-05 17:01

Updated:: 2020-11-11 14:52

Git Integration

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