How to add index to increase speed of mysql statement 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 &lt;= CURDATE()
    ORDER BY room_id , start_date DESC


When using Explain:



Stats of the query:
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).