解决or 和order by 语句查询时间长问题

表大小
24943*24943

1.原语句

SELECT A.ID1,A.ID2,A.relation FROM element_relations as A where ID1= 17 or ID2=17 order by relation DESC limit 300;

分别建立ID1,和ID2的索引

查看索引情况

索引id1，id2未用上
查询时间

查询时间

2.使用union all

(SELECT A.ID1,A.ID2,A.relation FROM element_relations as A where ID1= 1 order by relation DESC limit 300) union all (SELECT A.ID1,A.ID2,A.relation FROM element_relations as A where ID2= 1 order by relation DESC limit 300) order by relation DESC limit 300;

查看索引情况 查询时间

效果并不理想

3.重新建立索引

索引1：使用id1和relation联合索引
索引2：使用id3和relation联合索引
使用语句1

查看索引 查询时间

使用语句二 order by

索引情况 查询时间

已降为1秒左右，完美解决