How to speed up mysql query?

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?

• ThiefMaster: You really need to give us some more information: Which indexes do you have, how big are you tables, etc.
• Sebas: SHOW CREATE TABLE student-; SHOW CREATE TABLE degree_o; SHOW CREATE TABLE accredit
• kantu: I dont see any where conditions in this sql, so I assume the result set will be common for all. So why not use a view and update the view when the data changes or schedule a task so it changes updates the vie wevery day ?
• Aaron Brown: Make sure you have indexes on all the foreign keys. In this case, all of the fields named scid and then also on on school.ARCO_name. Post the explain plan for this query.

Answer 1

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`
;

• ypercubeᵀᴹ: This is not equivalent to the original query. Unless every school has only one student (and other assumptions are true).
• ypercubeᵀᴹ: It may give less or more rows in the result set. Or different ones.
• RolandoMySQLDBA: @ypercube Based on the original query, if school.scid=student-.scid, student-.scid=degree_o.scid, and degree_o.scid=accredit.scid, then school.scid has to be equal to degree_o.scid and accredit.scid. If Wolfe87 had used student_id to go from student- to the last two tables, then this redefines the query. In fact, the semantics would be different. I see your point in that a school has students, students have degrees, and degrees have accreditations. Since scid (which is school) was used throughout the original query, I rewrote the original query accordingly.
• ypercubeᵀᴹ: Even if scid is the Primary Key in all 4 tables, the 2 queries are not equivalent.
• RolandoMySQLDBA: @ypercube, you're right. They are not equivalent. The EXPLAIN plans should be differ greatly. Personally, I expect the EXPLAIN plan for my answer to look worse but perform better. I have seen this happen many times. I even tested this when I answered a question on StackOverflow (stackoverflow.com/a/6023217/491757) when I forced a LIMIT clause to execute earlier.