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

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

Experts,

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!
0
evibesmusic
Asked:
evibesmusic
  • 2
1 Solution
 
aikimarkCommented:
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.
0
 
evibesmusicAuthor Commented:
@aikimark:

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.

Cheers!
0
 
evibesmusicAuthor Commented:
@all:

Sorry...didn't realize that this question was not closed.
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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