Explain shows index doesnt working but i have an index

Question

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:

So in the users table i have this indexes

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 I forgot. The index for users as showed in the first image arent working for this query. You know what im doing bad?

UPDATE:

THE CREATE TABLE FOR users is:

CREATE TABLE `users` (
  `id` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth_date` date NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `verification_code` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activated` tinyint(1) NOT NULL DEFAULT 0,
  `access_token` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notifications` int(11) NOT NULL,
  `private` tinyint(1) NOT NULL DEFAULT 0,
  `blue_keys` int(11) NOT NULL DEFAULT 0,
  `orange_keys` int(11) NOT NULL DEFAULT 0,
  `playID` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `languaje` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'es',
  `first_login` tinyint(1) NOT NULL,
  `tutos_viewed` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `usuarioIndice` (`username`),
  KEY `tutosViewed_Users` (`tutos_viewed`),
  KEY `userIndex` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the SHOW CREATE TABLE for friendship is

CREATE TABLE `friendship` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `receiver` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT 0,
  `blocked` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `friendshipIndex` (`sender`),
  KEY `friendshipBlockedIndice` (`blocked`)
) ENGINE=InnoDB AUTO_INCREMENT=2132 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Answer 1

OR does not optimize well. The usual workaround is to reformulate the query to be the UNION of multiple SELECTs to look for the alternatives. However, your situation might work better with a second LEFT JOIN and COALESCE().

SELECT ..., COALESCE(r.sender, s.receiver), ...
    FROM ...
    LEFT JOIN ... AS r WHERE receiver = '...'
    LEFT JOIN ... AS s WHERE sender = '...'

Side notes:

• What the heck is the id that needs to be varchar(250)?
• There are two identical indexes on username; drop one.
• Switch from MyISAM to InnoDB. Virtually all arguments for MyISAM are "old wives' tales".