1

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
10
  • 1
    Have you an index on (value, timestamp)? Can you show the EXPLAIN ANALYZE for both queries? Also, the output of SHOW CREATE TABLE measurements\G.
    – Vérace
    Sep 20, 2017 at 12:35
  • If the query returns very fast ("instantly"), I suggest you keep it. Sep 20, 2017 at 12:41
  • 1
    @Vérace a better index would be on (tag, timestamp, value). Sep 20, 2017 at 12:42
  • @ypercubeᵀᴹ - yes, of course, much better! I would be curious though to see why the simple MIN()/MAX() query takes so much longer... Sounds like an iffy optimiser plan to me!
    – Vérace
    Sep 20, 2017 at 12:58
  • @ypercubeᵀᴹ The question has been updated. I don't have an index on value. By my understanding MySQL doesn't have EXPLAIN ANALYZE. So i used profiling and EXPLAIN EXTENDED instead. Sep 20, 2017 at 13:17

1 Answer 1

0

Simple answer, no. But as a fun exercise the following will work in MySQL (see further down for obvious downsides):

NOTE: The code was changed back to match your own, but was tested with different fields and tables and worked. min/max slowness confirmed.

DROP VIEW IF EXISTS simple_view;
CREATE VIEW simple_view AS SELECT `value`, `timestamp`
   FROM `measurements`
   WHERE 
     `timestamp` > '2017-07-14 13:00:00'
     AND `timestamp` < '2017-08-25 13:00:00';

SELECT x.value AS 'min', y.value AS 'max' 
FROM 
  ( SELECT * from simple_view order by `timestamp` ASC LIMIT 1) as x,
  ( SELECT * from simple_view order by `timestamp` DESC LIMIT 1) as y;

DROP VIEW IF EXISTS simple_view;

Obvious downsides include (but not limited to):

  • Views can be slow (in this case as fast as your original in testing)
  • You can only have one of these running at a time, unless you somehow make the view name unique each time.
  • It really doesn't save you much
  • You need to remember to clean up after yourself.

A better solution is to simplify the query and build it on the fly in a language outside of MySQL so you don't care about repetition.

2
  • @antonio This and your method obviously only work because of the relationship between timestamp and value (i.e. value is always growing). But it will break if this relationship is ever broken - for example if the meter is replaced.
    – Voltaire
    Sep 20, 2017 at 13:27
  • That is correct. But the meter gives impulses to my controller, so in an event that the meter is changed the counter will continue. Sep 20, 2017 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.