The query in MySQL is:
EXPLAIN EXTENDED SELECT id,username,name,last_name,IF(friend IS NOT NULL,1,0) as friend,activated,if(private=1 AND IF(friend IS NOT NULL,1,0)=1,0,private) as private FROM users LEFT JOIN (SELECT
CASE
WHEN sender='asdf10e84b5fd1b1' THEN receiver
WHEN receiver='asdf10e84b5fd1b1' THEN sender
END AS friend
FROM `friendship` WHERE (sender='asdf10e84b5fd1b1' OR receiver='asdf10e84b5fd1b1') and accepted=1) as f
ON friend=id WHERE activated=1 ORDER BY friend DESC LIMIT 100
And the EXPLAIN EXTENDED says this: [![Image ][1]][1]
So in the users table i have this indexes
[![Indexes][2]][2]
And the great problem and why i'm using indexes in this problem is because the query is large and the time is 4 sec so when 10,000 users are checking the same the query is slow x10000.
Help me pls [1]: https://i.sstatic.net/plDkz.png [2]: https://i.sstatic.net/K9CVN.png
I forgot. The index for users as showed in the first image arent working for this query. You know what im doing bad?