How to optimize this mulit-JOIN query so that it executes faster?

Posted on 2014-08-15
Last Modified: 2014-08-22

On average the following query takes 5 seconds to execute.  Is there any way to optimize the query so that it takes less time to execute?

SELECT employees.EmpId, employees.FirstName, employees.Lastname, employees.HomeSite, employees.HomeDepartment, employees.UserField13, employees.ManagerNUID, employees.HomeDepartment, employees.HomeSite, schedule.JobStartTime, schedule.JobEndTime, jobs.JobDepartment, jobs.JobSite FROM employees JOIN schedule ON employees.EmpId=schedule.EmpId JOIN jobs ON schedule.JobAssignmentId=jobs.JobAssignmentId 
	WHERE schedule.DeleteFlag!='1' AND employees.ManagerNUID LIKE '$user' AND schedule.ScheduleforDate='".date('Y-m-d', strtotime(str_replace('-', '/', $stoppage_date)))." 00:00:00' AND jobs.UserField3!='1' ORDER BY employees.HomeSite, employees.HomeDepartment, employees.Lastname, employees.FirstName, schedule.JobStartTime ASC

Open in new window

Thank you in advance for your help!
Question by:evibesmusic
    LVL 44

    Accepted Solution

    1. make sure there are indexes on all the joined fields.

    employees.ManagerNUID LIKE '$user'
    2. Unless you are using some wildcard matching, this should run faster with an equality string comparison:
    employees.ManagerNUID = '$user'

    3. Your casting of a string field to a date value isn't very efficient and is probably the primary cause of your 'slow' performance.  Make sure that both columns are the same data type to avoid/eliminate data conversions in the query.

    Author Comment


    Thank you very much. I know my questions was very novice.

    I've only implemented suggestion 1 of your solution and the query is already 3-4 times faster.

    I will continue updating my code using your suggestions and hope to see even more improved execution times.


    Author Closing Comment


    Sorry...didn't realize that this question was not closed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now