0

How do I increase the speed of this mysql statement? taking 1.10s but was wondering if it could be made faster.

SELECT 
        res_rooms.room_id AS room,
        res_rooms.type AS room_type,
        res_rooms.status AS room_status,
        IFNULL(utilizations.user_id, - 1) AS user_id,
        CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
        CONCAT_WS(0, system_users.name, camps.s_first) AS name,
        IFNULL(utilizations.start_date, NULL) AS start_date,
        IFNULL(utilizations.end_date, NULL) AS end_date,
        utilizations.keys_issued AS keys_issued,
        utilizations.resource_type AS resource_type,
        utilizations.staff_id AS staff_id,
        CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
        CONCAT_WS(0,
                students.date_of_birth,
                camps.date_of_birth) AS dob,
        CONCAT_WS(0, students.gender, camps.sex) AS gender,
        CONCAT_WS(0, students.status, camps.status) AS status,
        countries.common_name AS citizenship,
        CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
        IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
        IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
    FROM
        res_rooms
            LEFT JOIN
        utilizations ON utilizations.resource_id = res_rooms.room_id
            LEFT JOIN
        system_users ON system_users.user_id = utilizations.user_id
            LEFT JOIN
        camps ON camps.camp_id = utilizations.user_id
            LEFT JOIN
        students ON students.user_id = utilizations.user_id
            LEFT JOIN
        countries ON (students.citizenship = countries.code
            || camps.citizenship = countries.code)
            LEFT JOIN
        fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
    WHERE
        (students.status = 'B'
            OR students.status = 'VC')
            AND utilizations.start_date <= CURDATE()
    ORDER BY room_id , start_date DESC

When using Explain:

enter image description here

Stats of the query:

enter image description here

6
  • 1
    Most of the LEFT JOINs actually return an INNER JOIN result due to the WHERE-conditions. And an ORed join is usually quite bad. Try repalcing it by ´UNIONing two Selects (copy the Select and use one of the join conditions to countries`.
    – dnoeth
    Jul 21 '17 at 13:49
  • could you explain this more?
    – apex
    Jul 21 '17 at 14:17
  • 1
    Adding more information to the question would help us help you. In particular: the total number of rows you get when you run the query as is; table and index definitions for the tables involved (if not full table definitions, then at least the critical columns - primary key, any foreign keys); if possible, the query plan used when the query is run (check docs on EXPLAIN for how to do this).
    – RDFozz
    Jul 21 '17 at 18:06
  • updated question.
    – apex
    Jul 21 '17 at 18:11
  • Let's see SHOW CREATE TABLE; sounds like the indexes or datatypes are 'bad'.
    – Rick James
    Jul 22 '17 at 14:26
2
+50

You should add an indexes for each table having NULL in the possible_keys of EXPLAIN. Columns used for index should be the same used for JOINs. In case of countries table you have to add an index for code field while corresponding tables students and camps should be indexed on the citizenship fields each.

The common rule is:

When you join two tables with ON condition, each table should have indexed columns used for ON.

SELECT * 
  FROM a
  JOIN b ON a.foo = b.bar;

Here table a should have index (foo) while table b should have index (bar)

When you filter out rows by WHERE clause, columns mentioned in the WHERE conditions should be indexed:

SELECT * 
  FROM a
 WHERE a.foo = 1234;

Here table a should have index (foo).

Complex conditions that refers to the multiple columns in the same table require multicolumn indexes:

SELECT * 
  FROM a
 WHERE a.foo = 1234
   AND a.bar BETWEEN '2017-07-20' AND '2017-07-21';

Here table a should have an multicolumn index (foo, bar) or (bar,foo), or even both. It's depends on certain data stored in your table and some performance measurements should be performed. Even though for different queries different indexes can become optimal and day-wide range could be faster with (foo,bar) while month-wide range could be faster with (bar,foo).

8
  • Do I add indexes for all columns of tables that are NULL in the possible_keys of EXPLAIN?
    – apex
    Jul 24 '17 at 13:38
  • updated question.
    – apex
    Jul 24 '17 at 14:03
  • Is it possible the query cant be made faster?
    – apex
    Jul 24 '17 at 14:20
  • I got it working. Finally understanding where i need to use indexes too. Thanks. I'll award bounty in 22 hours when I can!
    – apex
    Jul 24 '17 at 14:29
  • The main sign of the problem is the using filesort message in the EXPLAINs output. You can have some indexes for all the tables but if some of them are inappropriate for certain query you'll end up with performance losses. Avoid Using filesort at any cost because that drive to the creation of huge on-disk tempopary tables that are slow.
    – Kondybas
    Jul 24 '17 at 14:43
1

The joins to utilizations and students are actually Inner Join as the WHERE-conditions will remove the NULLs created by the Outer Join.

The join to countries is really bad as it's using an ORed condition and joins three tables at the same time (optmizers choke on that).

Switch to Inner Joins whenever possible (MySQL's optimizer used to be bad in recognizing that an Outer Join is actually a Inner).

And try replacing the ORed join with UNION:

SELECT 
    res_rooms.room_id AS room,
    res_rooms.type AS room_type,
    res_rooms.status AS room_status,
    IFNULL(utilizations.user_id, - 1) AS user_id,
    CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
    CONCAT_WS(0, system_users.name, camps.s_first) AS name,
    IFNULL(utilizations.start_date, NULL) AS start_date,
    IFNULL(utilizations.end_date, NULL) AS end_date,
    utilizations.keys_issued AS keys_issued,
    utilizations.resource_type AS resource_type,
    utilizations.staff_id AS staff_id,
    CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
    CONCAT_WS(0,
            students.date_of_birth,
            camps.date_of_birth) AS dob,
    CONCAT_WS(0, students.gender, camps.sex) AS gender,
    CONCAT_WS(0, students.status, camps.status) AS status,
    countries.common_name AS citizenship,
    CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
    IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
    IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
FROM
    res_rooms
        JOIN
    utilizations ON utilizations.resource_id = res_rooms.room_id
        LEFT JOIN
    system_users ON system_users.user_id = utilizations.user_id
        LEFT JOIN
    camps ON camps.camp_id = utilizations.user_id
        JOIN
    students ON students.user_id = utilizations.user_id
        LEFT JOIN
    countries ON students.citizenship = countries.code
        LEFT JOIN
    fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
WHERE
    (students.status = 'B'
        OR students.status = 'VC')
        AND utilizations.start_date <= CURDATE()

UNION

SELECT 
    res_rooms.room_id AS room,
    res_rooms.type AS room_type,
    res_rooms.status AS room_status,
    IFNULL(utilizations.user_id, - 1) AS user_id,
    CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
    CONCAT_WS(0, system_users.name, camps.s_first) AS name,
    IFNULL(utilizations.start_date, NULL) AS start_date,
    IFNULL(utilizations.end_date, NULL) AS end_date,
    utilizations.keys_issued AS keys_issued,
    utilizations.resource_type AS resource_type,
    utilizations.staff_id AS staff_id,
    CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
    CONCAT_WS(0,
            students.date_of_birth,
            camps.date_of_birth) AS dob,
    CONCAT_WS(0, students.gender, camps.sex) AS gender,
    CONCAT_WS(0, students.status, camps.status) AS status,
    countries.common_name AS citizenship,
    CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
    IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
    IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
FROM
    res_rooms
        JOIN
    utilizations ON utilizations.resource_id = res_rooms.room_id
        LEFT JOIN
    system_users ON system_users.user_id = utilizations.user_id
        LEFT JOIN
    camps ON camps.camp_id = utilizations.user_id
        JOIN
    students ON students.user_id = utilizations.user_id
        LEFT JOIN
    countries ON camps.citizenship = countries.code
        LEFT JOIN
    fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
WHERE
    (students.status = 'B'
        OR students.status = 'VC')
        AND utilizations.start_date <= CURDATE()
ORDER BY room_id , start_date DESC

Or maybe better replace it by two joins to countries plus COALESCE:

SELECT 
    res_rooms.room_id AS room,
    res_rooms.type AS room_type,
    res_rooms.status AS room_status,
    IFNULL(utilizations.user_id, - 1) AS user_id,
    CONCAT_WS(0, system_users.surname, camps.s_surname) AS surname,
    CONCAT_WS(0, system_users.name, camps.s_first) AS name,
    IFNULL(utilizations.start_date, NULL) AS start_date,
    IFNULL(utilizations.end_date, NULL) AS end_date,
    utilizations.keys_issued AS keys_issued,
    utilizations.resource_type AS resource_type,
    utilizations.staff_id AS staff_id,
    CONCAT_WS(0, students.handled_by, camps.handled_by) AS handled_by,
    CONCAT_WS(0,
            students.date_of_birth,
            camps.date_of_birth) AS dob,
    CONCAT_WS(0, students.gender, camps.sex) AS gender,
    CONCAT_WS(0, students.status, camps.status) AS status,
    COALESCE(c1.common_name, c2.common_name) AS citizenship,
    CONCAT_WS(0, students.applying_for, camps.applying) AS applying_for,
    IFNULL(fee_schedule.ResiLife_type, 'N/A') AS residence_type,
    IFNULL(fee_schedule.Resi_Amount, 'N/A') AS reslife_amount
FROM
    res_rooms
        JOIN
    utilizations ON utilizations.resource_id = res_rooms.room_id
        LEFT JOIN
    system_users ON system_users.user_id = utilizations.user_id
        LEFT JOIN
    camps ON camps.camp_id = utilizations.user_id
        JOIN
    students ON students.user_id = utilizations.user_id
        LEFT JOIN
    countries AS c1 ON students.citizenship = c1.code
        LEFT JOIN
    countries AS c2 ON camps.citizenship = c2.code
        LEFT JOIN
    fee_schedule ON utilizations.user_id = fee_schedule.Stud_id
WHERE
    (students.status = 'B'
        OR students.status = 'VC')
        AND utilizations.start_date <= CURDATE()
ORDER BY room_id , start_date DESC
9
  • makes it slower
    – apex
    Jul 21 '17 at 14:46
  • Which one, both? Then you might check if you got the right indexes.
    – dnoeth
    Jul 21 '17 at 14:48
  • the first one is around 1.9s and the second is about 1.2s. And I tried adding indexes but not sure if I did it right.
    – apex
    Jul 21 '17 at 14:48
  • So this doesn't seem to be related to the actual SQL, but indexing (or the actual size of the tables). To facilitate the joins the join columns are usualy indexed and the columns in WHERE
    – dnoeth
    Jul 21 '17 at 14:51
  • Anything I can read for this? I'm a bit confused.
    – apex
    Jul 21 '17 at 15:36

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.