I can't provide our dataset because It's really very large( 1.2 T) I picked some queries from the slow log , and bellow is the result of explain : 5.6.10: mysql> explain select t.feed_id, t.rated_uid, t.rated_user_nick, t.rater_uid, t.rater_user_nick, t.rater_type, t.status, t.validscore, t.rate, t.trade_id, t.parent_trade_id, t.gmt_trade_create, t.gmt_trade_finished, t.auc_num_id, t.auction_title, t.auction_pic_url, t.auction_price, t.auction_virtual, t.leaf_cat_id, t.root_cat_id, t.spu_id, t.feedback, t.feedback_length, t.validfeedback, t.reply, t.explain_delete_time, t.last_modify_from, t.edit_status, t.biz_type, t.source, t.buy_state, t.rater_pic, t.properties, t.anony, t.privacy, t.album_id, t.vote_useful, t.rater_star, t.sort_weight, t.attributes, t.number_1, t.number_2, t.number_3, t.gmt_create, t.gmt_modified, t.lock_version, t.feedback_ip from ( select feed_id from feed_item_receive_0981 where rated_uid = 47926229 and rater_type = 0 and biz_type IN ( 0 ) and status not in ( 1 , 2 ) order by gmt_create desc limit 0,51 ) t1, feed_item_receive_0981 t force index(primary) where t.feed_id=t1.feed_id order by gmt_create desc; +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------+--------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------+--------+------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 51 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | t1.feed_id | 1 | NULL | | 2 | DERIVED | feed_item_receive_0981 | ref | uk_feed_item_receive,ind_feed_item_receive_rid,ind_feed_item_receive_rdidn,ind_feed_item_receive_stat,ind_feed_item_receive_ridr,ind_feed_item_receive_ridgr | ind_feed_item_receive_ridr | 11 | const,const,const | 272586 | Using where; Using index; Using filesort | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------+--------+------------------------------------------+ PS5.5.18 root@feedback 11:09:02> explain select t.feed_id, t.rated_uid, t.rated_user_nick, t.rater_uid, t.rater_user_nick, t.rater_type, t.status, t.validscore, t.rate, t.trade_id, t.parent_trade_id, t.gmt_trade_create, t.gmt_trade_finished, t.auc_num_id, t.auction_title, t.auction_pic_url, t.auction_price, t.auction_virtual, t.leaf_cat_id, t.root_cat_id, t.spu_id, t.feedback, t.feedback_length, t.validfeedback, t.reply, t.explain_delete_time, t.last_modify_from, t.edit_status, t.biz_type, t.source, t.buy_state, t.rater_pic, t.properties, t.anony, t.privacy, t.album_id, t.vote_useful, t.rater_star, t.sort_weight, t.attributes, t.number_1, t.number_2, t.number_3, t.gmt_create, t.gmt_modified, t.lock_version, t.feedback_ip from ( select feed_id from feed_item_receive_0981 where rated_uid = 47926229 and rater_type = 0 and biz_type IN ( 0 ) and status not in ( 1 , 2 ) order by gmt_create desc limit 0,51 ) t1, feed_item_receive_0981 t force index(primary) where t.feed_id=t1.feed_id order by gmt_create desc; +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+------------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+------------+---------+---------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 51 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | t1.feed_id | 1 | | | 2 | DERIVED | feed_item_receive_0981 | ALL | uk_feed_item_receive,ind_feed_item_receive_rid,ind_feed_item_receive_rdidn,ind_feed_item_receive_stat,ind_feed_item_receive_ridr,ind_feed_item_receive_ridgr | ind_feed_item_receive_ridr | 11 | | 7809835 | Using filesort | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+------------+---------+---------------------------------+ 5.6.10 mysql> explain select t.feed_id, t.rated_uid, t.rated_user_nick, t.rater_uid, t.rater_user_nick, t.rater_type, t.status, t.validscore, t.rate, t.trade_id, t.parent_trade_id, t.gmt_trade_create, t.gmt_trade_finished, t.auc_num_id, t.auction_title, t.auction_pic_url, t.auction_price, t.auction_virtual, t.leaf_cat_id, t.root_cat_id, t.spu_id, t.feedback, t.feedback_length, t.validfeedback, t.reply, t.explain_delete_time, t.last_modify_from, t.edit_status, t.biz_type, t.source, t.buy_state, t.rater_pic, t.properties, t.anony, t.privacy, t.album_id, t.vote_useful, t.rater_star, t.sort_weight, t.attributes, t.number_1, t.number_2, t.number_3, t.gmt_create, t.gmt_modified, t.lock_version, t.feedback_ip from ( select feed_id from feed_item_receive_1011 where rater_type = 0 and biz_type IN ( 0 ) and auc_num_id = 16470546605 and status in ( 0 ) and validfeedback = 1 order by sort_weight desc limit 0,21 ) t1, feed_item_receive_1011 t force index(primary) where t.feed_id=t1.feed_id order by t.sort_weight desc ; +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+---------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 8 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | t1.feed_id | 1 | NULL | | 2 | DERIVED | feed_item_receive_1011 | ref | uk_feed_item_receive,ind_feed_item_receive_aidg,ind_feed_item_receive_aidsr,ind_feed_item_receive_stat | ind_feed_item_receive_aidsr | 13 | const,const,const,const,const | 8 | Using where; Using index | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+---------------------------------+ 3 rows in set (0.01 sec) PS5.5.18 root@feedback 11:09:56> explain select t.feed_id, t.rated_uid, t.rated_user_nick, t.rater_uid, t.rater_user_nick, t.rater_type, t.status, t.validscore, t.rate, t.trade_id, t.parent_trade_id, t.gmt_trade_create, t.gmt_trade_finished, t.auc_num_id, t.auction_title, t.auction_pic_url, t.auction_price, t.auction_virtual, t.leaf_cat_id, t.root_cat_id, t.spu_id, t.feedback, t.feedback_length, t.validfeedback, t.reply, t.explain_delete_time, t.last_modify_from, t.edit_status, t.biz_type, t.source, t.buy_state, t.rater_pic, t.properties, t.anony, t.privacy, t.album_id, t.vote_useful, t.rater_star, t.sort_weight, t.attributes, t.number_1, t.number_2, t.number_3, t.gmt_create, t.gmt_modified, t.lock_version, t.feedback_ip from ( select feed_id from feed_item_receive_1011 where rater_type = 0 and biz_type IN ( 0 ) and auc_num_id = 16470546605 and status in ( 0 ) and validfeedback = 1 order by sort_weight desc limit 0,21 ) t1, feed_item_receive_1011 t force index(primary) where t.feed_id=t1.feed_id order by t.sort_weight desc ; +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------+------+---------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 8 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | t1.feed_id | 1 | | | 2 | DERIVED | feed_item_receive_1011 | ref | uk_feed_item_receive,ind_feed_item_receive_aidg,ind_feed_item_receive_aidsr,ind_feed_item_receive_stat | ind_feed_item_receive_aidsr | 13 | | 8 | Using where; Using index | +----+-------------+------------------------+--------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------+------+---------------------------------+ 3 rows in set (0.18 sec) 5.6.10 mysql> explain select count(feed_id) from ( select feed_id from feed_item_receive_1002 where rater_type = 0 and biz_type IN ( 0 ) and auc_num_id = 16147782923 and status in ( 0 ) and validfeedback = 1 limit 101 ) temp; +----+-------------+------------------------+------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+-------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 8 | NULL | | 2 | DERIVED | feed_item_receive_1002 | ref | uk_feed_item_receive,ind_feed_item_receive_aidg,ind_feed_item_receive_aidsr,ind_feed_item_receive_stat | ind_feed_item_receive_aidsr | 13 | const,const,const,const,const | 8 | Using index | +----+-------------+------------------------+------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------+------+-------------+ 2 rows in set (0.03 sec) PS5.5.18 root@feedback 11:10:59> explain select count(feed_id) from ( select feed_id from feed_item_receive_1002 where rater_type = 0 and biz_type IN ( 0 ) and auc_num_id = 16147782923 and status in ( 0 ) and validfeedback = 1 limit 101 ) temp; +----+-------------+------------------------+------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+------+------+------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | | 2 | DERIVED | feed_item_receive_1002 | ref | uk_feed_item_receive,ind_feed_item_receive_aidg,ind_feed_item_receive_aidsr,ind_feed_item_receive_stat | ind_feed_item_receive_aidsr | 13 | | 8 | Using index | +----+-------------+------------------------+------+--------------------------------------------------------------------------------------------------------+-----------------------------+---------+------+------+------------------------------+ 2 rows in set (0.17 sec) 5.6.10 explain select t.feed_id, t.rated_uid, t.rated_user_nick, t.rater_uid, t.rater_user_nick, t.rater_type, t.status, t.validscore, t.rate, t.trade_id, t.parent_trade_id, t.gmt_trade_create, t.gmt_trade_finished, t.auc_num_id, t.auction_title, t.auction_pic_url, t.auction_price, t.auction_virtual, t.leaf_cat_id, t.root_cat_id, t.spu_id, t.feedback, t.feedback_length, t.validfeedback, t.reply, t.explain_delete_time, t.last_modify_from, t.edit_status, t.biz_type, t.source, t.buy_state, t.rater_pic, t.properties, t.anony, t.privacy, t.album_id, t.vote_useful, t.rater_star, t.sort_weight, t.attributes, t.number_1, t.number_2, t.number_3, t.gmt_create, t.gmt_modified, t.lock_version, t.feedback_ip from ( select feed_id from feed_item_send_1016 where rater_uid = 34968568 and biz_type IN ( 0 ) and status in ( 0 ) and validfeedback = 1 AND anony = 0 order by gmt_create desc limit 0,21 ) t1, feed_item_send_1016 t force index(primary) where t.feed_id=t1.feed_id order by t.gmt_create desc; +----+-------------+---------------------+--------+-------------------------+-------------------------+---------+-------------------------+-------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+--------+-------------------------+-------------------------+---------+-------------------------+-------+------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 21 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | t1.feed_id | 1 | NULL | | 2 | DERIVED | feed_item_send_1016 | ref | ind_feed_item_send_rrid | ind_feed_item_send_rrid | 12 | const,const,const,const | 66592 | Using where; Using index; Using filesort | +----+-------------+---------------------+--------+-------------------------+-------------------------+---------+-------------------------+-------+------------------------------------------+ PS5.5.18 root@feedback 11:10:09>explain select t.feed_id, t.rated_uid, t.rated_user_nick, t.rater_uid, t.rater_user_nick, t.rater_type, t.status, t.validscore, t.rate, t.trade_id, t.parent_trade_id, t.gmt_trade_create, t.gmt_trade_finished, t.auc_num_id, t.auction_title, t.auction_pic_url, t.auction_price, t.auction_virtual, t.leaf_cat_id, t.root_cat_id, t.spu_id, t.feedback, t.feedback_length, t.validfeedback, t.reply, t.explain_delete_time, t.last_modify_from, t.edit_status, t.biz_type, t.source, t.buy_state, t.rater_pic, t.properties, t.anony, t.privacy, t.album_id, t.vote_useful, t.rater_star, t.sort_weight, t.attributes, t.number_1, t.number_2, t.number_3, t.gmt_create, t.gmt_modified, t.lock_version, t.feedback_ip from ( select feed_id from feed_item_send_1016 where rater_uid = 34968568 and biz_type IN ( 0 ) and status in ( 0 ) and validfeedback = 1 AND anony = 0 order by gmt_create desc limit 0,21 ) t1, feed_item_send_1016 t force index(primary) where t.feed_id=t1.feed_id order by t.gmt_create desc; +----+-------------+---------------------+--------+-------------------------+-------------------------+---------+------------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+--------+-------------------------+-------------------------+---------+------------+---------+---------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 21 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 8 | t1.feed_id | 1 | | | 2 | DERIVED | feed_item_send_1016 | ALL | ind_feed_item_send_rrid | ind_feed_item_send_rrid | 12 | | 6413913 | Using filesort | +----+-------------+---------------------+--------+-------------------------+-------------------------+---------+------------+---------+---------------------------------+ 3 rows in set (0.18 sec) 5.6.10 mysql> explain select count(feed_id) from ( select feed_id from feed_item_send_0988 where rater_uid = 411651036 and biz_type IN ( 0 ) and status in ( 0 ) and validfeedback = 1 AND anony = 0 limit 101 ) temp; +----+-------------+---------------------+------+-------------------------+-------------------------+---------+-------------------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+------+-------------------------+-------------------------+---------+-------------------------+-------+--------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 101 | NULL | | 2 | DERIVED | feed_item_send_0988 | ref | ind_feed_item_send_rrid | ind_feed_item_send_rrid | 12 | const,const,const,const | 36400 | Using where; Using index | +----+-------------+---------------------+------+-------------------------+-------------------------+---------+-------------------------+-------+--------------------------+ 2 rows in set (0.01 sec) PS5.5.18 root@feedback 11:11:37> explain select count(feed_id) from ( select feed_id from feed_item_send_0988 where rater_uid = 411651036 and biz_type IN ( 0 ) and status in ( 0 ) and validfeedback = 1 AND anony = 0 limit 101 ) temp; +----+-------------+---------------------+------+-------------------------+-------------------------+---------+------+-------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+------+-------------------------+-------------------------+---------+------+-------+------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | | 2 | DERIVED | feed_item_send_0988 | ref | ind_feed_item_send_rrid | ind_feed_item_send_rrid | 12 | | 36400 | Using where; Using index | +----+-------------+---------------------+------+-------------------------+-------------------------+---------+------+-------+------------------------------+