Query optimisation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Query optimisation

Post 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
Last edited by hessodreamy on Wed Sep 21, 2005 9:48 am, edited 2 times in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

test and find out ;)
hessodreamy
Forum Commoner
Posts: 58
Joined: Wed Apr 20, 2005 8:11 am

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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.)
Post Reply