mysql query slow with joins

satch_boogie

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 |             |
+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+------+----------+-------------+
Philipp

Try setting a btree-index for ntv_staff_office.pid.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related