开发者

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)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜