MySQL performance using where

Question

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 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 |
+----+-------------+-------+------+--------------------+--------+---------+-------+--------+----------------------------------------------+

As far as normalcy goes, usually you can set indices that remove the need for the filesort, and often the temporary table as well. However, I'm not one of those MySQL wizards who can do that :P — Matchu
– Matchu, Commented Jul 12, 2011 at 1:34
try creating a key on (symbol, time, feed). also try not to use DATE() in your where clause — knittl
– knittl, Commented Jul 12, 2011 at 1:35

gbn · Accepted Answer · 2011-07-12 01:40:53Z

6

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)

edited Jul 12, 2011 at 1:40

answered Jul 12, 2011 at 1:34

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frankie Over a year ago

95 rows in set (0.05 sec), outstanding. Thank you! Would you care to explain a bit more on why? Going to get some rest now. Will accept tomorrow (9 more minutes still to allow accept).

Collectives™ on Stack Overflow

MySQL performance using where

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related