MySQL query very slow without an explicit "USE INDEX"

Question

I've got a weird situation where my MySQL query is taking forever. I've fixed it by adding an explicit "USE INDEX" statement. My question really is why is this necessary - and what caused the MySQL optimiser to go so drastically wrong.

Here's the SQL statement:

SELECT i._id
FROM interim_table i
JOIN tablea a ON i.table_a_id = a._id
JOIN tableb b ON i.table_b_id = b._id
JOIN levels l ON a.level_id = l._id
JOIN projects p ON a.project_id = p._id
WHERE s.time_stamp > NOW() - INTERVAL 5 DAY AND a.project_id = 13

Note the time_stamp in the WHERE clause. If it's set to 5 days, then the query takes about two seconds. If I change it to 6 days however, it takes that long that MySQL times out.

This is the "explain" results for using the "5 day" interval (which takes 2 seconds):

id   select_type   table   type    possible_keys                    key                key_len   ref            rows  Extra
1    SIMPLE        p       const   PRIMARY                          PRIMARY            4         const          1     Using index
1    SIMPLE        b       range   PRIMARY, time_stamp              time_stamp         8                        479   Using where; Using index
1    SIMPLE        i       ref     ind_tableb_id,int_tablea_id      ind_tableb_id      4         b._id          11   
1    SIMPLE        a       eq_ref  PRIMARY,level_id,project_id      PRIMARY            4         i.table_a_id   1     Using where
1    SIMPLE        l       eq_ref  PRIMARY                          PRIMARY            4         a.level_id     1     Using index

This is the "explain" results for using the "6 day" interval (which times out):

id   select_type   table   type    possible_keys                    key              key_len     ref                       rows   Extra
1    SIMPLE        p       const   PRIMARY                          PRIMARY          4           const                     1      Using index
1    SIMPLE        a       ref     PRIMARY,level_id,project_id      project_id       4           const                     2722                          
1    SIMPLE        l       eq_ref  PRIMARY                          PRIMARY          4           a.level_id     1      Using index
1    SIMPLE        i       ref     ind_tableb_id,int_tablea_id      int_tablea_id    4           a._id          2                          
1    SIMPLE        b       eq_ref  PRIMARY,time_stamp               PRIMARY          4           i.table_b_id   1      Using where

If I put an explicit "USE INDEX" statement in there, then I get the 6 day interval also down to 2 seconds...

SELECT i._id
FROM interim_table i
JOIN tablea a ON i.table_a_id = a._id
JOIN tableb b USE INDEX (time_stamp) ON i.table_b_id = b._id
JOIN levels l ON a.level_id = l._id
JOIN projects p ON a.project_id = p._id
WHERE s.time_stamp > NOW() - INTERVAL 6 DAY AND a.project_id = 13

Then the explain results becomes:

id   select_type  table  type     possible_keys                  key             key_len  ref            rows    Extra
1    SIMPLE       p      const    PRIMARY                        PRIMARY         4        const          1       Using index
1    SIMPLE       s      range    time_stamp                     time_stamp      8                       504     Using where; Using index
1    SIMPLE       i      ref      ind_tableb_id,ind_tableaid     ind_tableb_id   4        s._id          11                            
1    SIMPLE       v      eq_ref   PRIMARY,level_id,project_id    PRIMARY         4        i.table_a_id   1       Using where
1    SIMPLE       l      eq_ref   PRIMARY                        PRIMARY         4        v.level_id     1       Using index

Any thoughts on why MySQL required me to tell it which index to use?

Answer 1

Have you tried to update statistics?

Posting it again as an answer :)