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
Query optimisation
Moderator: General Moderators
-
hessodreamy
- Forum Commoner
- Posts: 58
- Joined: Wed Apr 20, 2005 8:11 am
Query optimisation
Last edited by hessodreamy on Wed Sep 21, 2005 9:48 am, edited 2 times in total.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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 only return the values you need
The other important thing is rather than using
Code: Select all
SELECT * FROM X,Y WHERE X.col1=Y.col1Code: Select all
SELECT X.col1,field,nextfield FROM X,Y WHERE X.col1=Y.col1-
hessodreamy
- Forum Commoner
- Posts: 58
- Joined: Wed Apr 20, 2005 8:11 am
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
still be quicker than this:
and then get the supplierID and for each of them do this
I think it will but wanted to check.
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)Code: Select all
select p.prodName from products p WHERE prodID IN(43,44,45)Code: Select all
select s.supplierName from suppliers s WHERE supplierID = 'supplier'-
hessodreamy
- Forum Commoner
- Posts: 58
- Joined: Wed Apr 20, 2005 8:11 am
Sounds logical to mehessodreamy 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?
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)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.)
(Also, in your example above, you are not returning supplier ID from the products query, so could not actually check for it.)