0

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

  • Seems contact is from the users table. Then the WHERE-condition will change the result of the last join to an Inner Join, thus you can remove the last LEFT. – dnoeth Jun 20 '17 at 15:49
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.