We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Need help optimizing a MySQL query.

nbtc971
nbtc971 asked
on
Medium Priority
707 Views
Last Modified: 2013-11-15
How can I optimize this query:
select c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS ua USING(userid) WHERE j.jobid = 123;

Open in new window

Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Hope Your Jobs tables has jobid AS the PRIMARY KEY AND hence CREATE indexes ON the FOREIGN keys to get your queries perform better..

Other than that your joins are perfect and based upon Foreign keys directly ( I Guess).

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I know that the query works, but there has to be a way to optimize the query itself no?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Basically, its a clean query without much joins and where clauses and joins only on Primary and foreign keys.
That's why I said it is perfect earlier.

Author

Commented:
Thank you for your comments. The only reason I don't just give you the points and accept that answer as being complete is, I was told that the query could be optimized (that's all I was told). I too didn't see any way to optimize it other than indexing but the impression I got is, the query itself could be improved in some manner. Optimization is fairly new to me as normally if I get a query that works I run with it, but I would like this to run as well as it can.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
question: what is the join with useraccounts good for, it looks not necessary?

you could reverse the query, to make sure it checks the jobs table first.
also, please avoid using the USING syntax, it will only give you problems, and it is not portable to other databases.

anyhow, I do agree that your query cannot be "optimized" other than ensuring the proper indexes are there, or writing a more complex version like shown below as option 2

SELECT c.* 
  FROM jobs AS j 
  JOIN users as u
    ON u.userid = j.userid
  JOIN companies c
    ON c.companyid = u.userid
 WHERE j.jobid = 123
; 

option 2:
SELECT c.* 
  FROM companies c
 WHERE companyid in ( SELECT companyID from users u
                       WHERE userid in ( SELECT userid FROM jobs j
                                          WHERE j.jobid = 123
                                       )
                     )
;

Open in new window

Author

Commented:
Do you think option 2 would process faster? What do you think the benefits are? I personally can read option 2 more easily.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
it is possible that it runs faster, but not "for sure".
all a matter of testing and tuning
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
As long as the resultset in the subqueries are less, it will perform better.
And if the no of results for the subqueries increases, there will be a performance impact compared to your first query.

But I dont think even if the resultset is less, it wont perform much better compared to your original query.


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.