List:General Discussion« Previous MessageNext Message »
From:Milk Man Date:July 25 2000 10:52pm
Subject:Query Optimization for many to many
View as plain text  
Dear all,

First I want to thank Robert, Steve and the group for helping me get it.  
Does the order of the clauses of the AND operator make a difference in 
performance?  It should be I think.

SELECT * FROM Products, Join, Types WHERE Products.prodID = Join.prodID AND 
Join.typeID = Types.typeID AND Types.typeName LIKE 'AMD';

for the following 3 simple many-to-many tables.

Products:
---------
prodID  Description
1
2
3

Types:
-------
typeID  typeName
1       AMD
2       Intel

Join:
-----
prodID  typeID
1       1
1       2
2       1
3       2

Should I arrange the postion of the clause of the AND operator to optimize 
the query???  Should I have

..... WHERE Types.typeName LIKE 'AMD' AND Types.typeID = Join.typeID AND 
Join.prodID = Products.prodID;  (I think this is the fastest)

instead of

.....WHERE Types.typeName LIKE 'AMD' AND Join.prodID = Products.prodID AND 
Types.typeID = Join.typeID ;

(Is there any difference?)

instead of

.....WHERE Join.prodID = Products.prodID AND Types.typeID = Join.typeID  AND 
Types.typeName LIKE 'AMD';

I can manipulate it by many ways, but the short question is "will it be 
DIFFERENT?" The Join table has a few times more records but way smaller in 
size than the Products table.

Really appreciate it.

Thanks.

Milkman.



________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Thread
Query Optimization for many to manyMilk Man25 Jul
  • Re: Query Optimization for many to manyBenjamin Pflugmann27 Jul