Need help optimizing a MySQL query.

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

nbtc971Asked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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).
0
 
nbtc971Author Commented:
I know that the query works, but there has to be a way to optimize the query itself no?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nbtc971Author 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
nbtc971Author Commented:
Do you think option 2 would process faster? What do you think the benefits are? I personally can read option 2 more easily.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it is possible that it runs faster, but not "for sure".
all a matter of testing and tuning
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.


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.

All Courses

From novice to tech pro — start learning today.