force index(rank_score,rank_uploadtime,rank_personid);
结果还是一样,mysql使用了索引,但是在order by时却没有用了!上图是分析结果
表索引是什么、SQL语句是什么,贴出来看看
rank表索引: rank表结构: SQL语句:SELECT rank.rank_score,rank.rank_uploaduser FROM joye_rank AS rank INNER JOIN joye_person as person ON person_id=rank.rank_personId WHERE rank_score > 0 AND rank_uploadtime is not nullORDER BY rank_score DESC,rank_uploadtime DESC LIMIT 100;
不知道你仔细看过别人的回复没有, rank_score,rank_uploadtime,rank_personId 建立复合索引并强制使用索引试试SELECT * FROM table1 USE INDEX (上述复合索引名)
rank_score,rank_uploadtime,rank_personId
建立复合索引并强制使用索引试试
rank表结构:
SQL语句:SELECT rank.rank_score,rank.rank_uploaduser FROM joye_rank AS rank INNER JOIN joye_person as person ON person_id=rank.rank_personId WHERE rank_score > 0 AND rank_uploadtime is not nullORDER BY rank_score DESC,rank_uploadtime DESC LIMIT 100;
rank_score,rank_uploadtime,rank_personId
建立复合索引并强制使用索引试试SELECT * FROM table1 USE INDEX (上述复合索引名)
我也试过,把rank_personId放在最左边,结果是全表扫描!
先把
SELECT rank.rank_score,rank.rank_uploaduser FROM joye_rank AS rank WHERE rank_score > 0;这个查询出来,然为再做为临时表与另一个表连接,差不多应该就可以了!