Slow SELECT examining whole table

Question

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;

Answer 1

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.

Answer 2

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')