SQLite: optimize query with union

Question

This is my table definition:

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);

And this is my query + plan:

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

So the index is used, but there is still a scan and a b-tree involved in the order by. I tried getting rid of them by adding more indexes, but I don't get it working.

Any ideas of an index which gets rid of the scan?

Answer 1

Your query can be simplified to:

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;

and the query plan will be:

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

I don't see how you can get better than B-TREE in the ORDER BY clause since you are using a custom expression to sort by.

See the demo.