SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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


id
parent
notused
detail


5
0
0
SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=?)

35
0
0
USE TEMP B-TREE FOR ORDER BY

id	parent	notused	detail
5	0	0	SEARCH User USING INDEX User_X (thisUserBlockedCurrentUser=?)
35	0	0	USE TEMP B-TREE FOR ORDER BY