ojathelonius ojathelonius - 6 months ago 21
SQL Question

Low performance and timeout with my rest API

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
)
)

Answer

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.