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

Hi Doug and all, Thank you very much for your help! I have read about indexing but not understand completely. Per your three indexing suggestions, I understand the first and third ones. For the first indexing suggestion, my understanding is making the unique column index on prodID and typeID in the Join table, and name this created unique index as joinID. This joinID will be the primary key for the Join table. Is my understanding right, Doug? The second suggestion is indexing on typeID and joinID. So joinID is the primary key of Join Table right? (I didn't have the joinID field in the design when I emailed ask you, so I am a little confused) What is this unique multmilple column index on typeID and joinID for? I really appreciate it!!! Thank you Doug and anyone who join the discussion. Milkman.

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

________________________________________________________________________

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