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.