Page 1 of 1

Query optimisation

Posted: Wed Sep 21, 2005 5:52 am
by hessodreamy
Just wanted to ask which is the faster way of performing queries in this situation.
If I have a number of products, which may or may not be associated with a supplier, and I wanted to retrieve the product details along with details of their supplier, am I best off performing a left join between products and suppliers, or, for each product I have performing a separate query to get supplier details?
I'm going with the left join option but not 100% sure that its faster.
The tables feature about 45000 products and 80 suppliers.
A query will typically concern 4 or 5 products.

CHeers

Posted: Wed Sep 21, 2005 6:24 am
by CoderGoblin
Joins are normally far quicker. They are normally easier to maintain as well (change one SQL statement instead of possibly 2 or more).
The other important thing is rather than using

Code: Select all

SELECT * FROM X,Y WHERE X.col1=Y.col1
only return the values you need

Code: Select all

SELECT X.col1,field,nextfield FROM X,Y WHERE X.col1=Y.col1

Posted: Wed Sep 21, 2005 9:53 am
by hessodreamy
Thanks. Thats what I thought. Reading my question I think I should clarify though.
Given that left joins are so much slower than inner joins. Would a single query with a left join still be quicker than multiple queries with inner joins?

ie. would this

Code: Select all

select p.prodName, s.supplierName from products p LEFT JOIN suppliers s ON p.supplierID = s.supplierID WHERE prodID IN(43,44,45)
still be quicker than this:

Code: Select all

select p.prodName from products p WHERE prodID IN(43,44,45)
and then get the supplierID and for each of them do this

Code: Select all

select s.supplierName from suppliers s WHERE supplierID = 'supplier'
I think it will but wanted to check.

Posted: Wed Sep 21, 2005 10:00 am
by feyd
test and find out ;)

Posted: Thu Sep 22, 2005 3:07 am
by hessodreamy
OK here's another issue: Mysql is being a massive dran on our server cpu at present. Is it the case that queries that slower queries will necessarily cause a greater cpu load?

Posted: Thu Sep 22, 2005 5:01 am
by Weirdan
hessodreamy wrote:OK here's another issue: Mysql is being a massive dran on our server cpu at present. Is it the case that queries that slower queries will necessarily cause a greater cpu load?
Sounds logical to me :)

try to EXPLAIN your queries:

Code: Select all

explain select p.prodName, s.supplierName from products p LEFT JOIN suppliers s ON p.supplierID = s.supplierID WHERE prodID IN(43,44,45)
and watch for 'type: ALL', 'possible keys: NULL', 'using filesort', 'using temporary table' sentences in there. Then add relevant indexes (if they aren't in place already). This should greatly speed up your queries.

Posted: Thu Sep 22, 2005 5:46 pm
by Jean-Yves
The left join should be noticeably quicker as the population of products increases. If the number of products is small, the speed difference will be negligible on modern hardware.

(Also, in your example above, you are not returning supplier ID from the products query, so could not actually check for it.)