Question :
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?
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
- Perform
LIMIT 0, 25
on school table before the INNER JOINs - 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`
;