[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Optimize mysql query

Posted on 2012-09-14
2
Medium Priority
?
605 Views
Last Modified: 2012-09-14
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
Comment
Question by:smetterd
2 Comments
 
LVL 12

Assisted Solution

by:Jared_S
Jared_S earned 400 total points
ID: 38399165
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
 
LVL 25

Accepted Solution

by:
lwadwell earned 1600 total points
ID: 38399216
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question