similar mysql queries behave differently
-
Hi,
since upgrading to v6 one of my SQL-queries is slower than usual.
Similar queries are still fast like before the update.I don't know why the DB should threat them differently.
Slow Query:
SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' -> FROM idata_1177 d -> JOIN items i ON i.item_id = d.item_id -> WHERE i.item_id IN (38416,38421,38422) -> AND idata_timestamp BETWEEN <starttime> AND <endtime> -> ORDER BY idata_timestamp; describe idata_1177; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | item_id | int | NO | PRI | NULL | | | idata_timestamp | bigint | NO | PRI | NULL | | | idata_value | varchar(255) | YES | | NULL | | | raw_value | varchar(255) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ EXPLAIN SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' -> FROM idata_1177 d -> JOIN items i ON i.item_id = d.item_id -> WHERE i.item_id IN (38416,38421,38422) -> AND idata_timestamp BETWEEN <starttime> AND <endtime> -> ORDER BY idata_timestamp; +----+-------------+-------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | i | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | d | NULL | ref | PRIMARY | PRIMARY | 4 | netxms.i.item_id | 1355 | 11.11 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------------------+------+----------+----------------------------------------------+ 2 rows in set, 1 warning (0,00 sec) EXPLAIN ANALYZE SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' FROM idata_1177 d JOIN items i ON i.item_id = d.item_id WHERE i.item_id IN (38416,38421,38422) AND idata_timestamp BETWEEN <starttime> AND <endtime> ORDER BY idata_timestamp; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Sort: d.idata_timestamp (actual time=2041.314..2041.572 rows=4318 loops=1) -> Stream results (cost=418.24 rows=452) (actual time=978.482..2039.624 rows=4318 loops=1) -> Nested loop inner join (cost=418.24 rows=452) (actual time=978.464..2037.612 rows=4318 loops=1) -> Filter: (i.item_id in (38416,38421,38422)) (cost=3.63 rows=3) (actual time=0.086..0.144 rows=3 loops=1) -> Index range scan on i using PRIMARY over (item_id = 38416) OR (item_id = 38421) OR (item_id = 38422) (cost=3.63 rows=3) (actual time=0.084..0.137 rows=3 loops=1) -> Filter: (d.idata_timestamp between 1776340969454 and 1776427369454) (cost=7.70 rows=151) (actual time=678.666..679.068 rows=1439 loops=3) -> Index lookup on d using PRIMARY (item_id=i.item_id) (cost=7.70 rows=1355) (actual time=0.046..659.711 rows=525672 loops=3) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (2,05 sec)Fast Query:
SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' -> FROM idata_921 d -> JOIN items i ON i.item_id = d.item_id -> WHERE i.item_id IN (83253,83254) -> AND idata_timestamp BETWEEN <starttime> AND <endtime> -> ORDER BY idata_timestamp; describe idata_921; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | item_id | int | NO | PRI | NULL | | | idata_timestamp | bigint | NO | PRI | NULL | | | idata_value | varchar(255) | YES | | NULL | | | raw_value | varchar(255) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ EXPLAIN SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' -> FROM idata_921 d -> JOIN items i ON i.item_id = d.item_id -> WHERE i.item_id IN (83253,83254) -> AND idata_timestamp BETWEEN <starttime> AND <endtime> -> ORDER BY idata_timestamp; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ | 1 | SIMPLE | d | NULL | range | PRIMARY | PRIMARY | 12 | NULL | 2880 | 100.00 | Using where; Using filesort | | 1 | SIMPLE | i | NULL | eq_ref | PRIMARY | PRIMARY | 4 | netxms.d.item_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ 2 rows in set, 1 warning (0,00 sec) EXPLAIN ANALYZE SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' FROM idata_921 d JOIN items i ON i.item_id = d.item_id WHERE i.item_id IN (83253,83254) AND idata_timestamp BETWEEN <starttime> AND <endtime> ORDER BY idata_timestamp; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=3749.56 rows=2880) (actual time=1.607..5.474 rows=2880 loops=1) -> Sort: d.idata_timestamp (cost=581.56 rows=2880) (actual time=1.563..1.708 rows=2880 loops=1) -> Filter: ((d.item_id in (83253,83254)) and (d.idata_timestamp between 1776340969454 and 1776427369454)) (cost=581.56 rows=2880) (actual time=0.021..1.171 rows=2880 loops=1) -> Index range scan on d using PRIMARY over (item_id = 83253 AND 1776340969454 <= idata_timestamp <= 1776427369454) OR (item_id = 83254 AND 1776340969454 <= idata_timestamp <= 1776427369454) (cost=581.56 rows=2880) (actual time=0.018..0.876 rows=2880 loops=1) -> Single-row index lookup on i using PRIMARY (item_id=d.item_id) (cost=1.00 rows=1) (actual time=0.001..0.001 rows=1 loops=2880) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,01 sec) -
I've created a ticket for this: https://github.com/netxms/netxms/issues/3195
-
Hi Filipp,
thanks for creating an issue.
I checked the Query again today and noticed that for some reason, the exact same sql-query is now perfoming as expected by using the primary-key.
I didn't do any updates since creating this ticket. Maybe some chache issue from mysql.
mysql> EXPLAIN SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' FROM idata_1177 d JOIN items i ON i.item_id = d.item_id WHERE i.item_id IN (38416,38421,38422) AND idata_timestamp BETWEEN 1776340969454 AND 1776427369454 ORDER BY idata_timestamp; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ | 1 | SIMPLE | d | NULL | range | PRIMARY | PRIMARY | 12 | NULL | 4318 | 100.00 | Using where; Using filesort | | 1 | SIMPLE | i | NULL | eq_ref | PRIMARY | PRIMARY | 4 | netxms.d.item_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ mysql> EXPLAIN SELECT CONVERT(i.description, CHAR) 'metric', FLOOR(idata_timestamp/300000)*300000 'time', CONVERT(idata_value,DOUBLE) 'value' FROM idata_921 d JOIN items i ON i.item_id = d.item_id WHERE i.item_id IN (83253,83254) AND idata_timestamp BETWEEN 1776340969454 AND 1776427369454 ORDER BY idata_timestamp; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ | 1 | SIMPLE | d | NULL | range | PRIMARY | PRIMARY | 12 | NULL | 2880 | 100.00 | Using where; Using filesort | | 1 | SIMPLE | i | NULL | eq_ref | PRIMARY | PRIMARY | 4 | netxms.d.item_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-----------------------------+ -
Database query planning is a separate mystery on its own. Anyways, developers restored additional index as it was before v. 6.0, that will be included in next patch release.
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login