Skip to main content
2 of 4
added 122 characters in body
Slvr
  • 3
  • 3

Explain shows index doesnt working but i have an index

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?

Slvr
  • 3
  • 3