I'm using this sql query to get the largest and smallest value of electricity (value
of the tag electricity
is incremental).
SELECT x.value AS 'min',
y.value AS 'max' FROM
( SELECT `value`
FROM `measurements`
WHERE `tag`= 'electricity'
AND `timestamp` > '2017-07-14 13:00:00'
AND `timestamp` < '2017-08-25 13:00:00'
ORDER BY `timestamp` ASC
LIMIT 1) AS x,
(SELECT `value`
FROM `measurements`
WHERE `tag`= 'electricity'
AND `timestamp` > '2017-07-14 13:00:00'
AND `timestamp` < '2017-08-25 13:00:00'
ORDER BY `timestamp` DESC
LIMIT 1) AS y
Is it possible reduce the query size to use only one SELECT
but order it twice?
NOTE: Due to a large data-set (tested 100 million rows) using MIN and MAX the query takes more than 30 seconds. Doing it this way it returns instantly.
Per request:
Query above
+----+-------------+--------------+--------+---------------------------+-----------------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+--------+---------------------------+-----------------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 3 | DERIVED | measurements | range | tag_index,timestamp_index | timestamp_index | 8 | NULL | 9418306 | 2.01 | Using where |
| 2 | DERIVED | measurements | range | tag_index,timestamp_index | timestamp_index | 8 | NULL | 9418306 | 2.01 | Using where |
+----+-------------+--------------+--------+---------------------------+-----------------+---------+------+---------+----------+-------------+
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000030 |
| Waiting for query cache lock | 0.000005 |
| checking query cache for query | 0.000965 |
| checking permissions | 0.000010 |
| checking permissions | 0.000008 |
| Opening tables | 0.000046 |
| System lock | 0.000132 |
| optimizing | 0.000020 |
| statistics | 0.000316 |
| preparing | 0.000032 |
| executing | 0.000005 |
| Sorting result | 0.000031 |
| Sending data | 0.000141 |
| optimizing | 0.000012 |
| statistics | 0.000256 |
| preparing | 0.000019 |
| executing | 0.000004 |
| Sorting result | 0.000027 |
| Sending data | 0.000902 |
| Waiting for query cache lock | 0.000007 |
| Sending data | 0.000030 |
| init | 0.000018 |
| optimizing | 0.000006 |
| statistics | 0.000011 |
| preparing | 0.000007 |
| executing | 0.000004 |
| Sending data | 0.000017 |
| end | 0.000005 |
| query end | 0.000006 |
| closing tables | 0.000004 |
| removing tmp table | 0.000008 |
| closing tables | 0.000004 |
| removing tmp table | 0.000005 |
| closing tables | 0.000013 |
| freeing items | 0.000015 |
| Waiting for query cache lock | 0.000005 |
| freeing items | 0.000341 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000004 |
| storing result in query cache | 0.000006 |
| logging slow query | 0.000004 |
| cleaning up | 0.000007 |
+--------------------------------+----------+
Query with MIN & MAX
SELECT MAX(value), MIN(value) FROM measurements where `tag`= 'electricity' AND `timestamp` >= '2017-07-14 13:00:00' and `timestamp` < '2017-08-25 13:00:00' LIMIT 1;
+----+-------------+--------------+------+---------------------------+-----------+---------+-------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------+---------------------------+-----------+---------+-------+--------+----------+-------------+
| 1 | SIMPLE | measurements | ref | tag_index,timestamp_index | tag_index | 122 | const | 189664 | 100.00 | Using where |
+----+-------------+--------------+------+---------------------------+-----------+---------+-------+--------+----------+-------------+
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| starting | 0.000034 |
| Waiting for query cache lock | 0.000006 |
| checking query cache for query | 0.000109 |
| checking permissions | 0.000011 |
| Opening tables | 0.000026 |
| System lock | 0.000014 |
| Waiting for query cache lock | 0.000037 |
| init | 0.000059 |
| optimizing | 0.000031 |
| statistics | 0.000435 |
| preparing | 0.000030 |
| executing | 0.000010 |
| Sending data | 22.093571 |
| end | 0.000016 |
| query end | 0.000004 |
| closing tables | 0.000028 |
| freeing items | 0.000015 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000361 |
| Waiting for query cache lock | 0.000006 |
| freeing items | 0.000003 |
| storing result in query cache | 0.000005 |
| logging slow query | 0.000004 |
| logging slow query | 0.000005 |
| cleaning up | 0.000004 |
+--------------------------------+-----------+
*************************** 1. row ***************************
Table: measurements
Create Table: CREATE TABLE `measurements` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nad` int(11) NOT NULL,
`tag` varchar(40) NOT NULL,
`value` varchar(16) NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `tag_index` (`tag`),
KEY `timestamp_index` (`timestamp`),
KEY `nad` (`nad`)
) ENGINE=MyISAM AUTO_INCREMENT=115261277 DEFAULT CHARSET=utf8
(value, timestamp)
? Can you show theEXPLAIN ANALYZE
for both queries? Also, the output ofSHOW CREATE TABLE measurements\G
.(tag, timestamp, value)
.value
. By my understanding MySQL doesn't haveEXPLAIN ANALYZE
. So i used profiling andEXPLAIN EXTENDED
instead.