RE: [PHP-DB] Solved!--But Query Optimization for many-to-many tab les

Beware of table names (or column, ets) like 'Join' . . . it is an SQL reserved word and may behave very differently in diferent RDBMS's John T. Foley Network Administrator Pollak Engineered Products, Actuator Products Division, A Stoneridge Company 195 Freeport Street, Boston MA 02122 ph: (617) 474-7266 fax: (617) 282-9058 The geographical center of Boston is in Roxbury. Due north of the center we find the South End. This is not to be confused with South Boston which lies directly east from the South End. North of the South End is East Boston and southwest of East Boston is the North End. > -----Original Message----- > From: Doug Semig [SMTP:dougslist@c3net.net] > Sent: Tuesday, July 25, 2000 9:06 PM > To: Milk Man; php-db@lists.php.net > Subject: Re: [PHP-DB] 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. > > >