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.
我有一个奇怪的情况,我的MySQL查询将永远。我通过添加一个明确的“USE INDEX”声明来修复它。我的问题确实是为什么这是必要的 - 以及导致MySQL优化器出现如此严重错误的原因。
Here's the SQL statement:
这是SQL语句:
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.
请注意WHERE子句中的time_stamp。如果设置为5天,则查询大约需要两秒钟。但是,如果我将其更改为6天,则MySQL需要很长时间才能超时。
This is the "explain" results for using the "5 day" interval (which takes 2 seconds):
这是使用“5天”间隔(需要2秒)的“解释”结果:
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):
这是使用“6天”间隔(超时)的“解释”结果:
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...
如果我在那里放了一个明确的“USE INDEX”声明,那么我得到的6天间隔也会缩短到2秒......
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?
有关为什么MySQL要求我告诉它使用哪个索引的任何想法?
1 个解决方案
#1
2
Have you tried to update statistics?
您是否尝试更新统计信息?
Posting it again as an answer :)
再次发布它作为答案:)
#1
2
Have you tried to update statistics?
您是否尝试更新统计信息?
Posting it again as an answer :)
再次发布它作为答案:)