0

Can you please help me to understand, why this select examines 828004 rows (whole database) to return 21 rows?

# Time: 161121 14:00:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.507397  Lock_time: 0.000089 Rows_sent: 21  Rows_examined: 828004
SET timestamp=1479733244;
SELECT DATE(FROM_UNIXTIME(Epoch)) AS NodeDate, DeviceID as NodeID, Description, 
AVG(AD0) AS AverageAD0,AVG(AD1) AS AverageAD1 
FROM Data 
WHERE DATE(FROM_UNIXTIME(Epoch)) >= CURDATE() 
AND HOUR(FROM_UNIXTIME(Epoch)) >= HOUR(current_time()) 
GROUP BY NodeDate, NodeID 
ORDER BY AVG(AD0) DESC, AVG(AD0) DESC;

Data table structure

mysql> DESCRIBE Data;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(10)     | NO   | MUL | NULL    | auto_increment |
| Epoch       | int(10)     | NO   |     | NULL    |                |
| Address     | varchar(32) | NO   |     | NULL    |                |
| DeviceID    | varchar(32) | NO   |     | NULL    |                |
| Description | varchar(32) | NO   |     | NULL    |                |
| AD0         | int(10)     | NO   |     | NULL    |                |
| AD1         | int(10)     | NO   |     | NULL    |                |
| AD2         | int(10)     | NO   |     | NULL    |                |
| AnalogZero  | int(8)      | YES  |     | NULL    |                |
| Lat         | varchar(32) | NO   |     | NULL    |                |
| Lon         | varchar(32) | NO   |     | NULL    |                |
| RSSI        | varchar(8)  | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

Explain

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | Data  | ALL  | NULL          | NULL | NULL    | NULL | 822772 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+

Indexes

mysql> SHOW INDEX FROM Data;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data  |          1 | Id       |            1 | Id          | A         |      839767 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

The select is used to obtain averages from today AND current hour+.

This query is also examining all rows and looks super-simple. I have a 2-Core VPS and that is not handling such simple queries.

SELECT AVG(AD1) AS AverageAD1,AVG(AD2) AS AverageAD2, AVG(AD0) AS AverageAD0
FROM Data 
WHERE DeviceID="xxxxxxxx" 
AND FROM_UNIXTIME(Epoch) >= NOW() - INTERVAL 60 MINUTE;
  • 'cos it's doing an aggregation. Post your CREATE TABLE statement along with an EXPLAIN – Philᵀᴹ Nov 21 '16 at 13:30
  • Are you sure that the AND is needed? Now you get only the information after the dates after (or on) curdate() and on these dates only after HOUR(current_time()). – Marco Nov 21 '16 at 14:00
  • Typo? ORDER BY AVG(AD0) DESC, AVG(AD0) DESC; -- same thing twice? – Rick James Nov 22 '16 at 0:04
2

You want information based information that is in the Epoch field. Not even the whole field but just on DATE(FROM_UNIXTIME(Epoch)) and HOUR(FROM_UNIXTIME(Epoch)). Unless you have an index on that information the database will always need to read every row of the table to see if the row fits the criteria.

Adding an index on Epoch as a whole will not help too. The DATE(FROM_UNIXTIME(Epoch)) can be bypassed with (if it exists) TO_UNIXTIME(CURDATE()) but the HOUR(FROM_UNIXTIME(Epoch)) needs a specific index.

  • The select is used to obtain averages from TODAY AND current hour+ This QUERY is also examining all rows and looks super-simple. I have a 2Core VPS and that is not handling such simple queries... SELECT AVG(AD1) AS AverageAD1,AVG(AD2) AS AverageAD2, AVG(AD0) AS AverageAD0 FROM Data WHERE DeviceID="xxxxxxxx" AND FROM_UNIXTIME(Epoch) >= NOW() - INTERVAL 60 MINUTE; – PeeS Nov 21 '16 at 14:01
1

Do not hide a column inside a function if you need to index it:

WHERE DATE(FROM_UNIXTIME(Epoch)) >= CURDATE() 
  AND HOUR(FROM_UNIXTIME(Epoch)) >= HOUR(current_time())

Rewrite as

WHERE Epoch >= some-expression

and add INDEX(Epoch).

Assuming you are truncating to the start of the current hour, let's try something:

SELECT NOW(), LEFT(NOW(), 13)

I get

+---------------------+-----------------+
| NOW()               | LEFT(NOW(), 13) |
+---------------------+-----------------+
| 2016-11-21 15:37:13 | 2016-11-21 15   |
+---------------------+-----------------+

With luck, I don't need to play any more games:

WHERE Epoch >= LEFT(NOW(), 13)

If that does not work, the try

WHERE Epoch >= CONCAT(LEFT(NOW(), 13), ':00:00')
  • WHERE Epoch >= UNIX_TIMESTAMP(CONCAT(LEFT(NOW(), 13), ':00:00')) will be 100% correct form – a_vlad Nov 21 '16 at 23:49
  • CURDATE() + INTERVAL HOUR(NOW()) HOUR might be a better approach than messing up with strings. – Andriy M Nov 22 '16 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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