Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help optimizing a MySQL query.

Posted on 2009-02-15
8
Medium Priority
?
667 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

0
Comment
Question by:nbtc971
  • 3
  • 3
  • 2
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 23647314
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
 

Author Comment

by:nbtc971
ID: 23650713
I know that the query works, but there has to be a way to optimize the query itself no?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23650989
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
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.

 

Author Comment

by:nbtc971
ID: 23651587
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 23652660
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
 

Author Comment

by:nbtc971
ID: 23653125
Do you think option 2 would process faster? What do you think the benefits are? I personally can read option 2 more easily.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23653956
it is possible that it runs faster, but not "for sure".
all a matter of testing and tuning
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23657800
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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. …

564 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