asked on

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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nbtc971

ASKER

I know that the query works, but there has to be a way to optimize the query itself no?

Raja Jegan R

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.

nbtc971

ASKER

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.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nbtc971

ASKER

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]

it is possible that it runs faster, but not "for sure".
all a matter of testing and tuning

Raja Jegan R

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.