Return to Question

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

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?

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?

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

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?