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

Hi all, Doug, I think what you said you learned 8 years ago is still valid now if you use MySQL. Just for other people info: Page 183 DuBois' MySQL book says: index (state,city,zip) can be used to search the following combination: state, city, zip state, city state MySQL CANNOT use the index for searches that don't involve a leftmost prefix (reasonable enough). If you search by city or by zip the index isn't used. If you are searcing for a given state and a particular zip code, the index can't be used for the combination of values. However MySQL can narrow the search using the index to find rows that match the state. Thank you!!!

(Most likely, I've put you all to sleep.) Doug

***And Doug, keep going. You didn't put me to sleep even though it's 3:00 am. :)) Thanks again. Milkman.

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 ________________________________________________________________________

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