EXPLAIN SELECT id FROM aliai_base.base_score_summary
WHERE target_location = 21388 AND
target = 'courseware' AND
score_type = 'default'; 索引是target_location + target + score_type 时,居然是type = index。
而同样的sql语句,把索引改成:target + target_location + score_type ,(这与列的原始定义一致)
居然是type = ref
WHERE target_location = 21388 AND
target = 'courseware' AND
score_type = 'default'; 索引是target_location + target + score_type 时,居然是type = index。
而同样的sql语句,把索引改成:target + target_location + score_type ,(这与列的原始定义一致)
居然是type = ref
和 show index from base_score_summary
--------------------------------------------------------------------
Table Non_unique Key_name Seq_in_index Column_name
base_score_summary 0 PRIMARY 1 id A 1353
base_score_summary 0 TARGET 1 TARGET A 1
base_score_summary 0 TARGET 2 TARGET_LOCATION A 135
base_score_summary 0 TARGET 3 SCORE_TYPE A 1353
WHERE target_location = 21388 AND
target = 'courseware' AND
score_type = 'default';
结果:id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE base_score_summary ref TARGET TARGET 768 const 1380 Using where; Using index
现在把索引修改成 target_location + target + score_type,再explain结果:id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE base_score_summary index TARGET_LOCATION TARGET_LOCATION 2304 1411 Using where; Using index