使用我的rest API，性能低下并超时

Question

I am hosting a restful API created using Silex (a PHP micro-framework based on Symfony). Following this tutorial, this API returns json objects depending on the parameters. However some queries are so low that OVH's timeout kicks in and I get the following message :

SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

Although some of my SQL queries are a bit long (up to 8-12 lines), they aren't that complex and I don't understand why the performance is that low.

Is there anything I can do to speed it up, and to avoid being timed out by OVH ? I am querying every items using solely one SQL request (thus being long sometimes), and various operators (WHERE, several AND/OR, IN...).

Here's an example of a SQL query which takes forever to get :

SELECT *
FROM staff
WHERE staff_id IN (
        SELECT host_id
        FROM class_host
        WHERE class_id IN (
                SELECT class_id
                FROM class
                WHERE classroom_id IN (
                        SELECT classroom_id
                        FROM classroom
                        WHERE classroom_gps_lat BETWEEN $ARRAY_VALEUR [0]
                                AND $ARRAY_VALEUR [2]
                            AND classroom_gps_lon BETWEEN $ARRAY_VALEUR [1]
                                AND $ARRAY_VALEUR [3]
                        )
                )
        )
    OR staff_id IN (
        SELECT teacher_id
        FROM class_teacher
        WHERE class_id IN (
                SELECT class_id
                FROM class
                WHERE classroom_id IN (
                        SELECT classroom_id
                        FROM classroom
                        WHERE classroom_gps_lat BETWEEN $ARRAY_VALEUR [0]
                                AND $ARRAY_VALEUR [2]
                            AND classroom_gps_lon BETWEEN $ARRAY_VALEUR [1]
                                AND $ARRAY_VALEUR [3]
                        )
                )
        )
    AND class_id IN (
        SELECT class_id
        FROM class_teacher
        WHERE teacher_id IN (
                SELECT staff_id
                FROM staff
                WHERE staff_name LIKE $QUOTEDARRAY
                )
        )

Still having performance issues. My code went from this :

SELECT Count(*) AS COUNT 
FROM   staff 
WHERE  staff_id IN (SELECT host_id 
                    FROM   class_host 
                    WHERE  ( class_id IN (SELECT class_id 
                                          FROM   class 
                                          WHERE  classroom_id IN (SELECT 
                                                 classroom_id 
                                                                  FROM 
                                                 classroom 
                                                                  WHERE 
                                                 region_id IN ( 'FR' ))) ) 
                            OR staff_id IN (SELECT teacher_id 
                                            FROM   class_teacher 
                                            WHERE  class_id IN (SELECT class_id 
                                                                FROM   class 
                                                                WHERE 
                                                   classroom_id IN (SELECT 
                                                   classroom_id 
                                                                    FROM 
                                                   classroom 
                                                                    WHERE 
                                                   region_id IN (SELECT 
                                                   region_id 
                                                                 FROM   region 
                                                                 WHERE 
                                                   region_country_code IN ( 
                                                   'FR' )))))) 

to this :

SELECT Count(*) AS COUNT 
FROM   staff 
       JOIN class_host 
         ON staff.staff_id = class_host.host_id 
       JOIN class AS class1 
         ON class_host.class_id = class1.class_id 
       JOIN classroom AS classroom1 
         ON class1.classroom_id = classroom1.classroom_id 
       JOIN region AS region1 
         ON classroom1.region_id = region1.region_id 
       JOIN class_teacher 
         ON staff.staff_id = class_teacher.teacher_id 
       JOIN class AS class2 
         ON class_teacher.class_id = class2.class_id 
       JOIN classroom AS classroom2 
         ON class2.classroom_id = classroom2.classroom_id 
       JOIN region AS region2 
         ON classroom2.region_id = region2.region_id 
WHERE  region1.region_country_code IN ( 'FR' ) 
       AND region2.region_country_code IN ( 'FR' ) 

Should I use "faster" joins ? How can I optimize those queries ?

EDIT : EXPLAIN array from MySQL :

Explain from MySQL database for the query above

Answer 1

To get you started cleaning this up - Please note query is incomplete. This should be faster because you are not nesting selects.

SELECT
    staff.*
FROM 
    staff
    JOIN class_host ON staff.staff_id = class_host.host_id
    JOIN class ON class_host.class_id = class.class_id
    JOIN classroom ON classroom.classroom_id = class.classroom_id
WHERE
    classroom.gps_lat BETWEEN $ARRAY_VALEUR[0] AND $ARRAY_VALEUR[2]
    AND classroom.classroom_gps_lon BETWEEN $ARRAY_VALEUR[1] AND $ARRAY_VALEUR[3];

Once you have completed with joins - use Explain. EXPLAIN SELECT staff.* FROM ... which will help you to see where the query is running slow because chances are you are missing some indexes.