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?
OPTIMIZE TABLEon that table. The query planner does some guesstimating at how many rows it will have to look at with each plan, and sometimes decides that if it's going to have to scan almost the whole table to produce your result set, it should skip using the index to find the rows and just find them while reading the whole table to produce the results.