Mysql – Why a certain field causes a slow query

MySQLmysql-5.7performancequery-performance

I have 6 almost identical queries on a page. I am loading logs by keyname.

Lattitude and longitude take over 4s each, where as 4 other values take 0.09s How could just the one text value in where query cause that, see the 2 queries below.

This query takes 4 seconds:

SELECT
  h0_.keyname AS keyname_0,
  h0_.sensorvalue AS sensorvalue_1,
  h0_.receiveddate AS receiveddate_2,
  DATE_FORMAT(h0_.receiveddate, '%Y-%m-%d %H:%i') AS sclr_3
FROM history h0_
WHERE h0_.company_id = 1
  AND h0_.receiveddate > '2019-05-01 14:00'
  AND h0_.receiveddate < '2019-05-01 17:01'
  AND h0_.keyname = 'longitude'
  AND h0_.device_id = 9
GROUP BY sclr_3;

+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                            | key          | key_len | ref  | rows  | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | h0_   | NULL       | range | IDX_27BA704B979B1AD6,IDX_27BA704B94A4C7D4,receiveddate,keyname,device_id | receiveddate | 5       | NULL | 24022 |     0.15 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+

This query takes 0.09 seconds:

SELECT
  h0_.keyname AS keyname_0,
  h0_.sensorvalue AS sensorvalue_1,
  h0_.receiveddate AS receiveddate_2,
  DATE_FORMAT(h0_.receiveddate, '%Y-%m-%d %H:%i') AS sclr_3
FROM history h0_
WHERE h0_.company_id = 1
  AND h0_.receiveddate > '2019-05-01 14:00'
  AND h0_.receiveddate < '2019-05-01 17:01'
  AND h0_.keyname = 'temperature'
  AND h0_.device_id = 9
GROUP BY sclr_3;

+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                            | key          | key_len | ref  | rows  | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | h0_   | NULL       | range | IDX_27BA704B979B1AD6,IDX_27BA704B94A4C7D4,receiveddate,keyname,device_id | receiveddate | 5       | NULL | 24022 |     0.48 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+--------------------------------------------------------------------------+--------------+---------+------+-------+----------+---------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Here are some specifics:

  • mysql Ver 14.14 Distrib 5.7.26, for Linux
  • Ubuntu 18
  • 6GB RAM
  • 2 CPU

This table has the following

    CREATE TABLE `history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `device_id` int(11) DEFAULT NULL,
  `receiveddate` datetime NOT NULL,
  `keyname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sensorvalue` decimal(20,10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_27BA704B979B1AD6` (`company_id`),
  KEY `IDX_27BA704B94A4C7D4` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Best Answer

I see nothing to explain the timing difference. However, I see some things to improve. They might even improve on the 0.09s.

WHERE h0_.company_id = 1
  AND h0_.receiveddate > '2019-05-01 14:00'
  AND h0_.receiveddate < '2019-05-01 17:01'
  AND h0_.keyname = 'longitude'
  AND h0_.device_id = 9

Begs for a 'composite' index:

INDEX(company_id, key_name, device_id, -- in any order
      receivedate)

This is a strange range; it is 3 hours + 1 minute - 1 second:

  AND h0_.receiveddate > '2019-05-01 14:00'
  AND h0_.receiveddate < '2019-05-01 17:01'

decimal(20,10) seems excessive. It takes 10 bytes and has more precision than virtually any consumer-grade sensor. Consider a 4-byte FLOAT (with no (m,n)).

Are you receiving multiple values in a minute? If so, the GROUP BY is improper. The output will pick one of the possible sensorvalues and receivedates, and they may even correspond to each other. Either remove the GROUP BY or do some kind of aggregate, such as AVG().

How many different keyname values? Is 255 needed in varchar(255)? Do you have millions of rows, and this is a rather bulky part of the table? See also ENUM.

Related Question