Mysql – How to speed up thesql query

MySQLoptimizationperformancePHPquery-performance

I have query :

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

It's working and everything but it need about 20 seconds to execute.
Is something wrong in my query?
What can I do to speed up process?

Best Answer

Here is your original query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM school
    INNER JOIN `student-` ON `school`.`scid`   = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `degree_o`.`scid` = `accredit`.`scid`
    ORDER BY `school`.`ARCO_name` ASC LIMIT 0, 25
;

Two Suggestions

  1. Perform LIMIT 0, 25 on school table before the INNER JOINs
  2. INNER JOIN school.scid to the other three(3) tables

Here is my propsed query

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `school`.`scid` = `degree_o`.`scid`
    INNER JOIN `accredit` ON `school`.`scid` = `accredit`.`scid`
;

Give it a Try !!!

UPDATE 2012-06-25 12:13 EDT

To speed up the subquery, make sure you have this index:

ALTER TABLE school ADD INDEX ARCO_name_scid_ndx (ARCO_name,scid);

UPDATE 2012-06-25 12:18 EDT

AS pointed out by @yercube in his comments to my answer, you may want to attach the student-id from to degree_o and degree_id over to accedit. Perhaps something like this:

SELECT
    `school`.`ARCO_name`,
    `student-`.`ClassSize_7`,
    `student-`.`ClassSize_8`,
    `degree_o`.`degree_code`,
    `accredit`.`full_faculty_3`,
    `accredit`.`total_faculty_3`,
    `accredit`.`pc_terminal`,
    `accredit`.`stud_fac_ratio`
FROM
    (SELECT scid,ARCO_name FROM school ORDER BY ARCO_name LIMIT 0,25) school
    INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid`
    INNER JOIN `degree_o` ON `student-`.`studend_id` = `degree_o`.`studend_id`
    INNER JOIN `accredit` ON `degree_o`.`degree_id` = `accredit`.`degree_id`
;
Related Question