mysql query slow with joins

Question

upon enabling the slow_query_log in mysql. I found that the query below was one of slowest query with 15 secs or more. How can I optimize this query. All tables are Innodb and server: Server version: 5.6 Thank you

    EXPLAIN EXTENDED SELECT
    ntv_staffs.fname,
    ntv_staffs.id,
    ntv_staffs.mname,
    ntv_staffs.profile_pic,
    ntv_staffs.lname,
    ntv_staff_office.cug,
    ntv_staff_office.extension,
    ntv_staff_office.off_email,
    ntv_staff_office.job_title,
    ntv_designations.`name` as designation_name,
    ntv_branches.`name` as branch_name,
    ntv_departments.`name` as department_name,
    ntv_departments.id as department_id
    FROM
    ntv_staffs
    INNER JOIN ntv_staff_office ON ntv_staffs.id = ntv_staff_office.pid
    INNER JOIN ntv_departments ON ntv_staff_office.department_id = ntv_departments.id
    INNER JOIN ntv_branches ON ntv_staff_office.branch_id = ntv_branches.id
    INNER JOIN ntv_designations ON ntv_staff_office.designation = ntv_designations.id
    where ntv_staffs.id='662';


+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+------+----------+-------------+
| id | select_type | table            | type   | possible_keys | key     | key_len | ref                                          | rows | filtered | Extra       |
+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | ntv_staffs       | const  | PRIMARY       | PRIMARY | 4       | const                                        |    1 |   100.00 |             |
|  1 | SIMPLE      | ntv_staff_office | ALL    | NULL          | NULL    | NULL    | NULL                                         |  247 |   100.00 | Using where |
|  1 | SIMPLE      | ntv_branches     | eq_ref | PRIMARY       | PRIMARY | 4       | abc_portal_new.sks_staff_office.branch_id     |    1 |   100.00 |             |
|  1 | SIMPLE      | ntv_designations | eq_ref | PRIMARY       | PRIMARY | 4       | abc_portal_new.sks_staff_office.designation   |    1 |   100.00 | Using where |
|  1 | SIMPLE      | ntv_departments  | eq_ref | PRIMARY       | PRIMARY | 4       | abc_portal_new.sks_staff_office.department_id |    1 |   100.00 |             |
+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+------+----------+-------------+

Please have a look at http://stackoverflow.com/questions/6174246/mysql-innodb-query-performance — shankarsh15, May 24 '16 at 05:43

score 2 · Accepted Answer · edited May 24 '16 at 06:15

2

Try setting a btree-index for ntv_staff_office.pid.

edited May 24 '16 at 06:15

Felix Pamittan

31,544
7
41
67

answered May 24 '16 at 05:54

Philipp

2,787
2
25
27

mysql query slow with joins

1 Answers1