Re: Solved!--But Query Optimization for many-to-many tables

An excellent question! You have not only learned, but you've THOUGHT ABOUT WHAT YOU'VE LEARNED. I am very impressed! I am not a MySQL expert, though. However, I can say that most RDBMS implementators put a ton of work into "query optimization" routines. The theory is that your query will be parsed and then optimized by your particular RDBMS for your particular RDBMS. Since modelling a many-to-many relationship with 3 tables is extremely common, I would venture to say (with about a 99% certainty level) that your RDBMS will be able to optimally handle the conditions of the WHERE clause and the joining of the tables no matter what order you state them in. For optimization, you will want to probably simply build secondary indexes on your commonly used fields. Indexes are a trade-disk-space-for-speed consideration, but disk space nowadays is do dirt cheap that it usually takes about a minute of consideration to decide to use them. Here are some that might help: 1. a unique multiple column index on (prodID, typeID) in the Join table; 2. a unique multiple column index on (typeID, joinID) in the Join table; 3. a non-unique single column index on (typeNAME) in the Types table; You'll also want to have your RDBMS collect statistics from time to time on your tables so it can make more intelligent optimization decisions when doing query processing. I must admit that I do not know how to make MySQL do this, but someone should know. This is an important thing to do with other RDBMS implementations such as Oracle and PostgreSQL (of which I personally use PostgreSQL almost all the time nowadays). HTH, Doug Milk Man was heard at 06:45 PM 7/25/00 EDT to say: >Dear all, > >First I want to thank the group for helping me get it. This is the query: > >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; > >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 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. >