Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 25 '12 at 13:05

This question came from our site for professional and enthusiast programmers.

2  
You really need to give us some more information: Which indexes do you have, how big are you tables, etc. –  ThiefMaster Jun 25 '12 at 13:05
2  
SHOW CREATE TABLE student-; SHOW CREATE TABLE degree_o; SHOW CREATE TABLE accredit; –  Sebas Jun 25 '12 at 13:05
1  
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 ? –  kantu Jun 25 '12 at 13:05
2  
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. –  Aaron Brown Jun 25 '12 at 15:58

1 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`
;
share|improve this answer
1  
This is not equivalent to the original query. Unless every school has only one student (and other assumptions are true). –  ypercube Jun 25 '12 at 15:47
    
It may give less or more rows in the result set. Or different ones. –  ypercube Jun 25 '12 at 15:58
    
@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. –  RolandoMySQLDBA Jun 25 '12 at 16:11
    
Even if scid is the Primary Key in all 4 tables, the 2 queries are not equivalent. –  ypercube Jun 25 '12 at 16:15
    
@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. –  RolandoMySQLDBA Jun 25 '12 at 16:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.