SQLite:使用联合优化查询

Question

这是我的表格定义：

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

因此，索引被使用，但仍然有扫描和b-树涉及到的顺序。我试图通过添加更多的索引来摆脱它们，但我无法让它正常工作。

去掉scan的索引的任何想法

Answer 1

您的查询可以简化为：

SELECT *
FROM User
WHERE (friends = 1 OR recentSearchedTsCreated IS NOT NULL) 
  AND currentUserBlockedThisUserTsCreated IS NULL 
  AND thisUserBlockedCurrentUser = 0 
  AND username <> ''
ORDER BY CASE 
           WHEN friends = 1 THEN -2 
           WHEN recentSearchedTsCreated IS NOT NULL THEN -1 
           ELSE searchScreenScore 
         END,
         username;

查询计划是：

SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=?)
USE TEMP B-TREE FOR ORDER BY

我不知道如何在ORDER BY子句中获得比B树更好的效果，因为您正在使用自定义表达式进行排序。

见演示。