Mysql – How to improve speed of MYSQL query

MySQL

I am wondering how I can improve the speed of this query: 

SELECT 
    caf.user_id,
    caf.family_number,
    caf.card_number,
    caf.suspension_start_date,
    caf.suspension_end_date,
    caf.balance,
    util.room,
    caf.meal_plan_type,
    student.special_diet,
    student.specify_other,
    users.surname,
    users.type,
    users.name,
    users.contact
FROM
    (SELECT 
        *
    FROM
        caf_student_cards) AS caf
        LEFT JOIN
    (SELECT 
        user_id, special_diet, specify_other
    FROM
        students) AS student ON caf.user_id = student.user_id
        LEFT JOIN
    (SELECT 
        resource_id AS room, user_id
    FROM
        utilizations) AS util ON caf.user_id = util.user_id
        LEFT JOIN
    (SELECT 
        surname, name, type, user_id, contact
    FROM
        system_users) AS users ON caf.user_id = users.user_id
WHERE
    contact > 0;

Explain of query

Best Answer

While I don't do a lot of work in MySQL, at the least I can suggest a simplification of the query. In the FROM clause of the main query, JOIN the target tables directly, instead of making a subquery from each of the tables to join:

FROM
    caf_student_cards AS caf
    LEFT JOIN students AS student ON caf.user_id = student.user_id
    LEFT JOIN utilizations AS util ON caf.user_id = util.user_id
    LEFT JOIN system_users AS users ON caf.user_id = users.user_id

NOTE: You'll lose your renaming of util.resource_id, so you'll also need to change:

    util.room,

to:

    util.resource_id as room,

I can't think of any potential benefits of doing things the way you have (certainly not when your subquery is doing a SELECT *), and I've seen references that lead me to believe MySQL may be materializing the subqueries (meaning it's creating a copy of the table with the columns you list), which is likely to cause unnecessary extra work and resource consumption.

Related Question