How to make this SQL query faster?

Question

I have the following query:

SELECT DISTINCT `movies_manager_movie`.`id`, 
                 `movies_manager_movie`.`title`, 
                 `movies_manager_movie`.`original_title`, 
                 `movies_manager_movie`.`synopsis`, 
                 `movies_manager_movie`.`keywords`, 
                 `movies_manager_movie`.`release_date`, 
                 `movies_manager_movie`.`rating`, 
                 `movies_manager_movie`.`poster_web_url`, 
                 `movies_manager_movie`.`has_poster`, 
                 `movies_manager_movie`.`number`, 
                 `movies_manager_movie`.`has_sources`, 
                 `movies_manager_movie`.`season_id`, 
                 `movies_manager_movie`.`created`, 
                 `movies_manager_movie`.`updated`, 
                 `movies_manager_moviecache`.`activity_name` 

FROM `movies_manager_movie` 

LEFT OUTER JOIN `movies_manager_moviecache` ON (`movies_manager_movie`.`id` = `movies_manager_moviecache`.`movie_id`) 

WHERE (`movies_manager_movie`.`has_sources` = 1  
       AND (`movies_manager_moviecache`.`team_member_id` IN (

          SELECT U0.`id` FROM `movies_manager_movieteammember` U0 
          INNER JOIN `movies_manager_movieteammemberactivity` U1 ON (U0.`id` = U1.`team_member_id`) 
          WHERE U1.`movie_id` = 3588 ) 
                AND `movies_manager_movie`.`number` IS NULL
       ) 

    AND NOT (`movies_manager_movie`.`id` = 3588 )) 
    ORDER BY `movies_manager_moviecache`.`activity_name` DESC LIMIT 3;

This query can take up to 3 seconds and I'm very surprise since I got indexes everywhere and no more than 35 rows in each of my MyIsam tables, using the latest MySQL version.

I cached everything I could but I have at least to run this one 20000 times every day, which is approximately 16 h of waiting for loading. And I'm pretty sure none of my user (nor Google Bot) appreciate a 4 secondes waiting time for each page loading.

What could I do to make it faster ?

I thought about duplicating field from movie to moviecache since the all purpose of movie cache is to denormalize to complex join already.

I tried inlining the subquery to a list of ID but it surprisingly doubled the time of the query.

Tables:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment | 
| title          | varchar(120) | NO   | UNI | NULL    |                | 
| original_title | varchar(120) | YES  |     | NULL    |                | 
| synopsis       | longtext     | YES  |     | NULL    |                | 
| keywords       | varchar(120) | YES  |     | NULL    |                | 
| release_date   | date         | YES  |     | NULL    |                | 
| rating         | int(11)      | NO   |     | NULL    |                | 
| poster_web_url | varchar(255) | YES  |     | NULL    |                | 
| has_poster     | tinyint(1)   | NO   |     | NULL    |                | 
| number         | int(11)      | YES  |     | NULL    |                | 
| season_id      | int(11)      | YES  | MUL | NULL    |                | 
| created        | datetime     | NO   |     | NULL    |                | 
| updated        | datetime     | NO   |     | NULL    |                | 
| has_sources    | tinyint(1)   | NO   |     | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment | 
| name                | varchar(120) | NO   | UNI | NULL    |                | 
| biography           | longtext     | YES  |     | NULL    |                | 
| birth_date          | date         | YES  |     | NULL    |                | 
| picture_web_url     | varchar(255) | YES  |     | NULL    |                | 
| allocine_link       | varchar(255) | YES  |     | NULL    |                | 
| created             | datetime     | NO   |     | NULL    |                | 
| updated             | datetime     | NO   |     | NULL    |                | 
| has_picture         | tinyint(1)   | NO   |     | NULL    |                | 
| biography_linkyfied | longtext     | YES  |     | NULL    |                | 
+---------------------+--------------+------+-----+---------+----------------+

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment | 
| movie_id       | int(11)      | NO   | MUL | NULL    |                | 
| tag_slug       | varchar(100) | YES  | MUL | NULL    |                | 
| team_member_id | int(11)      | YES  | MUL | NULL    |                | 
| cast_rank      | int(11)      | YES  |     | NULL    |                | 
| activity_name  | varchar(30)  | YES  | MUL | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+

Mysql tells me it's a slow query:

# Query_time: 3  Lock_time: 0  Rows_sent: 9  Rows_examined: 454128

Answer 1

Try this:

SELECT `movies_manager_movie`.`id`, 
    `movies_manager_movie`.`title`, 
    `movies_manager_movie`.`original_title`, 
    `movies_manager_movie`.`synopsis`, 
    `movies_manager_movie`.`keywords`, 
    `movies_manager_movie`.`release_date`, 
    `movies_manager_movie`.`rating`, 
    `movies_manager_movie`.`poster_web_url`, 
    `movies_manager_movie`.`has_poster`, 
    `movies_manager_movie`.`number`, 
    `movies_manager_movie`.`has_sources`, 
    `movies_manager_movie`.`season_id`, 
    `movies_manager_movie`.`created`, 
    `movies_manager_movie`.`updated`, 
    (
        SELECT `movies_manager_moviecache`.`activity_name` 
        FROM `movies_manager_moviecache` 
        WHERE (`movies_manager_movie`.`id` = `movies_manager_moviecache`.`movie_id` 
            AND (`movies_manager_moviecache`.`team_member_id` IN (
                SELECT U0.`id` FROM `movies_manager_movieteammember` U0 
                    INNER JOIN `movies_manager_movieteammemberactivity` U1 ON (U0.`id` = U1.`team_member_id`) 
                WHERE U1.`movie_id` = 3588 ) 
            AND `movies_manager_movie`.`number` IS NULL
        ) ) LIMIT 1) AS `activity_name` 
FROM `movies_manager_movie`     
WHERE (`movies_manager_movie`.`has_sources` = 1  
    AND NOT (`movies_manager_movie`.`id` = 3588 )) 
ORDER BY `activity_name`  DESC 
LIMIT 3;

Let me know how that performs

Answer 2

Move movies_manager_movieteammemberactivity and movies_manager_movieteammember to your main join statement (so that you're doing a left outer between movies_manager_movie and the inner join product of the other 3 tables). This should speed up your query considerably.