这是我的表定义:
create table User (
userUuid text not null primary key,
username text not null,
thisUserBlockedCurrentUser int not null,
currentUserBlockedThisUserTsCreated int not null,
searchScreenScore int,
recentSearchedTsCreated int,
friends int not null
);
create index User_X on User(thisUserBlockedCurrentUser, friends);
这是我的查询+计划:
explain query plan
select *
from (select User.*
from User
where friends = 1
and User.currentUserBlockedThisUserTsCreated is null
and User.thisUserBlockedCurrentUser = 0
and User.username != ''
union
select User.*
from User
where recentSearchedTsCreated is not null
and User.currentUserBlockedThisUserTsCreated is null
and User.thisUserBlockedCurrentUser = 0
and User.username != '')
order by case when friends = 1 then -2 when recentSearchedTsCreated is not null then -1 else searchScreenScore end,
username;
CO-ROUTINE (subquery-2)
COMPOUND QUERY
LEFT-MOST SUBQUERY
SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=? AND friends=?)
UNION USING TEMP B-TREE
SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=?)
SCAN (subquery-2)
USE TEMP B-TREE FOR ORDER BY
所以使用了索引,但是在order by中仍然有一个扫描和一个b树。我试着通过添加更多的索引来摆脱它们,但是我没有让它工作。
有没有关于去掉scan
的索引的想法?
1条答案
按热度按时间2w3rbyxf1#
您的查询可以简化为:
查询计划将为:
我看不出如何在
ORDER BY
子句中获得比B-TREE更好的结果,因为您正在使用自定义表达式来排序。请参阅demo。