• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

Optimize mysql query

Where can I make indexes or update this query to make it run faster?

----------

SELECT distinct SR_STU_ENROLL.CAMPUS_ID AS School, SR_STU_ENROLL.STATE_STU_ID AS Ssn, SR_STU_DEMO.STU_ID AS StudId, SR_STU_ENROLL.NAME_F AS NameFirst, SR_STU_ENROLL.NAME_M AS NameMiddle, SR_STU_ENROLL.NAME_L AS NameLast, CONCAT(SR_STU_ENROLL.NAME_L,', ',SR_STU_ENROLL.NAME_F,' ',SR_STU_ENROLL.NAME_M) AS NameFull, SR_STU_ENROLL.GRD_LVL AS Grade, SR_STU_ENROLL.STATUS_CD AS `Status`, SR_STU_ENROLL.DT_ENTRY AS CurStatusDt, SR_STU_ENROLL.DT_ENTRY AS EntryDt, SR_STU_DEMO.DOB AS Birthday, CONCAT(SR_PARENT.PHONE_AREA_CD,SR_PARENT.PHONE_NBR) AS phone, CONCAT(SR_PARENT.NAME_L,', ',SR_PARENT.NAME_F,' ',SR_PARENT.NAME_M) AS ParentName, CONCAT(SR_PARENT.STR_NBR,' ',SR_PARENT.STR_NAME,' ',SR_PARENT.APT_NBR) AS ParentReside, CONCAT(SR_PARENT.CITY,' ',SR_PARENT.STATE_CD) AS ParentAddr, SR_PARENT.ZIP AS ParentZip5 FROM SR_STU_DEMO RIGHT OUTER JOIN SR_STU_ENROLL ON SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID RIGHT OUTER JOIN SR_PARENT ON SR_PARENT.STU_ID = SR_STU_ENROLL.STU_ID WHERE SR_STU_ENROLL.SCH_YR = '2013'
0
smetterd
Asked:
smetterd
2 Solutions
 
Jared_SCommented:
The fields where you filter (joins, and where clauses) are great places to put indexes.

SR_STU_ENROLL.STU_ID
SR_STU_DEMO.STU_ID
SR_PARENT.STU_ID
SR_STU_ENROLL.STU_ID
SR_STU_ENROLL.SCH_YR
0
 
lwadwellCommented:
I would eliminate any outer joins that are not necessary.  You have
FROM SR_STU_DEMO
RIGHT OUTER JOIN SR_STU_ENROLL ON SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID
RIGHT OUTER JOIN SR_PARENT ON SR_PARENT.STU_ID = SR_STU_ENROLL.STU_ID
WHERE SR_STU_ENROLL.SCH_YR = '2013'

As you are using right outer joins ... the right most table is SR_PARENT making it the mandatory table.  As you have a where clause of SR_STU_ENROLL.SCH_YR = '2013' ... this invalidates the outer join, so it could be made an inner join leaving SR_STU_DEMO  as the only outer table left.  Thus the FROM/WHERE could be
FROM SR_PARENT
INNER JOIN SR_STU_ENROLL ON SR_PARENT.STU_ID = SR_STU_ENROLL.STU_ID
LEFT OUTER JOIN SR_STU_DEMO ON SR_STU_ENROLL.STU_ID = SR_STU_DEMO.STU_ID
WHERE SR_STU_ENROLL.SCH_YR = '2013'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now