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;
Best Answer
You want information based information that is in the
Epoch
field. Not even the whole field but just onDATE(FROM_UNIXTIME(Epoch))
andHOUR(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. TheDATE(FROM_UNIXTIME(Epoch))
can be bypassed with (if it exists)TO_UNIXTIME(CURDATE())
but theHOUR(FROM_UNIXTIME(Epoch))
needs a specific index.