MySQL performance using where
A simple query like the one below, properly indexed on a table populated with roughly 2M rows is taking 95 rows in set (2.06 sec)
a lot longer to complete than I was hoping for.
As this is my first experience with tables this size, am I looking into normal behavior?
Query:
SELECT t.id, t.symbol, t.feed, t.time,
FLOOR(UNIX_TIMESTAMP(t.time)/(60*15)) as diff
FROM data as t
WHERE t.symbol = 'XYZ'
AND DATE(t.time) = '2011-06-02'
AND t.feed = '1M'
GROUP 开发者_JS百科BY diff
ORDER BY t.time ASC;
...and Explain
:
+----+-------------+-------+------+--------------------+--------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------+--------+---------+-------+--------+----------------------------------------------+
| 1 | SIMPLE | t | ref | unique,feed,symbol | symbol | 1 | const | 346392 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+--------------------+--------+---------+-------+--------+----------------------------------------------+
Try this:
...
AND t.time >= '2011-06-02' AND t.time < '2011-06-03'
...
Otherwise, your index(es) are wrong for this query. I'd expect one on (symbol, feed, time, id)
or (feed, symbol, time, id)
to cover it.
Edit, after comment:
If you put a function or processing on a column, any index is liable to be ignored. The index is on x
not f(x)
basically.
This change allows the index to be used because we now do a <= x < y
to ignore the time part, not takeofftime(x)
精彩评论