How to add index to increase speed of mysql statement

Question

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:

Stats of the query:

Answer 1

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

Answer 2

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