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

Oops...you're right. "joinID" should have been "typeID" in the 2nd index suggestion. There would, of course, be no "joinID." Actually, one of those suggested indexes would be your primary key, anyway. So it wouldn't really be a secondary index at all. How about this revision: 1. Join (see the previous discussion why it would not be a good idea to name a table "Join") table would have a primary key on (prodID, typeID). 2. Join table would have a secondary index on (typeID, prodID). 3. Types table would have a secondary index on (typeNAME). Why have an index on (typeID, prodID) when the reverse, (prodID, typeID), already has a key built on it? Good question. I concede that it may not be necessary with modern RDBMS products. It's a throwback from when I learned all this stuff over eight years ago. There was a DBMS package that we used on an IBM mainframe way back then that was unable to use the index/key to select records based upon the 2nd column in the index. We learned this the hard way, and I've been doing this ever since. Here's what we learned... Let's say you have a table like this: Sample_join_table ----------------- prodID typeID ------ ------ 1 1 1 2 1 3 2 3 3 1 3 2 The primary key is, of course, (prodID, typeID), and the table is shown in ascending order of the key. If you wrote a SELECT that used "typeID = 2" in the WHERE clause, you would think that all the DBMS would have to do is use the key and directly pull out the second and last row very quickly. This wasn't the case because the typeID field is listed second in the key definition. Instead, it required a full scan to pull out just those two rows. When we added a secondary index on (typeID, prodID), the records when viewed in this order would be like this: prodID typeID ------ ------ 1 1 3 1 1 2 3 2 1 3 2 3 so the DBMS could do a binary search on the secondary index and get quickly to the third row...and it would keep returning rows until it hit the fifth record, which does not satisfy "typeID = 2" in the WHERE clause. I admit that I do not know what MySQL, PostgreSQL, Oracle, Sybase, DB2, and other modern RDBMS products are able to do in this case. Now that I think about it, I imagine that by now they have probably solved this seemingly simple quite-often-done thing. But over eight years ago it caused me quite a lot of grief. Does anyone have a simple many-to-many relationship in any populated development databases to could get a query plan for this kind of situation? (Most likely, I've put you all to sleep.) Doug Nam Nguyen was heard at 07:42 PM 7/26/00 EDT to say: > >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; >> ... snip ...