explain select im.item_id from property_value p left join item i on p.id = i.value left join
item_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM
`property` WHERE `key_p` = 1) LIMIT 24 , 8
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY im ref class,item_id class 4 const 9096 Using where
1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where
2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where
explain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join
item_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM
`property` WHERE `key_p` = 1) group by im.item_id LIMIT 20 , 8id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where
1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where
2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where explain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join
item_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%') and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM
`property` WHERE `key_p` = 1) group by im.item_id order by count(im.item_id) LIMIT 20 , 8id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where; Using temporary; Using filesort
1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where
2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where 我要用得第3条sql,我要order by的是count(im.item_id)这个数,请问如何优化,附索引,谢谢SHOW INDEX FROM item_message Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
item_message 0 PRIMARY 1 id A 11994 NULL NULL BTREE
item_message 1 class 1 class A 3 NULL NULL BTREE
item_message 1 regional 1 regional A 3 NULL NULL YES BTREE
item_message 1 city 1 city A 11 NULL NULL YES BTREE
item_message 1 regional_city 1 regional A 3 NULL NULL YES BTREE
item_message 1 regional_city 2 city A 11 NULL NULL YES BTREE
item_message 1 create_date 1 create_date A 3 NULL NULL YES BTREE
item_message 1 item_id 1 item_id A 11994 NULL NULL BTREE SHOW INDEX FROM item Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
item 0 PRIMARY 1 id A 1148679 NULL NULL BTREE
item 0 value 1 value A 1148679 NULL NULL BTREE
item 1 item_id 1 item_id A 143584 NULL NULL BTREE
item 1 class_id 1 class_id A 3 NULL NULL BTREE SHOW INDEX FROM property_value Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
property_value 0 PRIMARY 1 id A 1149757 NULL NULL BTREE
property_value 1 property_id 1 property_id A 23 NULL NULL BTREE
property_value 1 property_id 2 value A 6495 255 NULL BTREE
property_value 1 value 1 value NULL 4892 NULL NULL FULLTEXT
item_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM
`property` WHERE `key_p` = 1) LIMIT 24 , 8
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY im ref class,item_id class 4 const 9096 Using where
1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where
2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where
explain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join
item_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM
`property` WHERE `key_p` = 1) group by im.item_id LIMIT 20 , 8id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where
1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where
2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where explain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join
item_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%') and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM
`property` WHERE `key_p` = 1) group by im.item_id order by count(im.item_id) LIMIT 20 , 8id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where; Using temporary; Using filesort
1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where
2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where 我要用得第3条sql,我要order by的是count(im.item_id)这个数,请问如何优化,附索引,谢谢SHOW INDEX FROM item_message Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
item_message 0 PRIMARY 1 id A 11994 NULL NULL BTREE
item_message 1 class 1 class A 3 NULL NULL BTREE
item_message 1 regional 1 regional A 3 NULL NULL YES BTREE
item_message 1 city 1 city A 11 NULL NULL YES BTREE
item_message 1 regional_city 1 regional A 3 NULL NULL YES BTREE
item_message 1 regional_city 2 city A 11 NULL NULL YES BTREE
item_message 1 create_date 1 create_date A 3 NULL NULL YES BTREE
item_message 1 item_id 1 item_id A 11994 NULL NULL BTREE SHOW INDEX FROM item Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
item 0 PRIMARY 1 id A 1148679 NULL NULL BTREE
item 0 value 1 value A 1148679 NULL NULL BTREE
item 1 item_id 1 item_id A 143584 NULL NULL BTREE
item 1 class_id 1 class_id A 3 NULL NULL BTREE SHOW INDEX FROM property_value Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
property_value 0 PRIMARY 1 id A 1149757 NULL NULL BTREE
property_value 1 property_id 1 property_id A 23 NULL NULL BTREE
property_value 1 property_id 2 value A 6495 255 NULL BTREE
property_value 1 value 1 value NULL 4892 NULL NULL FULLTEXT
因为count(im.item_id)这个新生成的值,所以这个只能内部会将查询结果防进临时表然后再排序,所以,这里面现有的基础上不用优化了另外,看你的执行计划,效果不错啊