"ManojW" <[EMAIL PROTECTED]> wrote on 04/13/2005 04:23:31 AM:
> Dear Shawn, > First off, apologies for the delay in reply to this email. > > Secondly, thanks a lot for a very illuminating dicussion on > composite keys and the way MySQL handles them. Reading through the > whole discussion, I have a minor question is popping up in my > heads...it is as follows: > > If I have a table with composite key fld1,fld2,fld3,fld4. My > normal way of handling the situation is to create a unique primary > key on (fld1,fld2,fld3,fld4) and then create "single" non-primary > indices on each of the remaining fields (so essentially three > indices - fld2-idx, fld3-idx and fld4-idx). > > Based on your experience, Is it more effective (in terms of > speed of query and cost of insert) to create a composite primary > index like (fld1-fld2-fld3-fld4), (fld2-fld1-fld-3-fld4) , (fld3- > fld1-fld2-fld4) and (fld4-fld1-fld2-fld3) thereby bringing all > fields (fld1 to fld 4) on the leftmost side.....OR Is it better to > create one composite primary key index (fld1-fld2-fld3-fld4) and > three single non-primary indices on fld2,fld3 and fld4 respectively > ? Any particular preference one way or another? > > Thanks! > > Cheers > > Manoj --------------<snip - see thread for previous responses>-------------------- I only create a primary index (also called a PRIMARY KEY or PK) when I need to ensure that no two records on that table share the values that are included in that key. Usually my PKs are just single columns but there are MANY valid reasons to use multi-column primary keys. The rest of my keys (indexes) are just plain indexes. I also "tune" my indexes based on how often certain queries are executed and how time-critical their results are. I do not recommend starting with an index for every possible combination of columns as that approach is generally overkill. For instance, if I run a query against values in 4 different columns but I don't need the response any time soon (say it's to calculate values for a monthly report) I don't need to create an index to support just that query. However, if you have a web-based front end and you notice certain query patterns slowing down your site and appearing in your slow query log (you do have yours turned on, don't you?) then you need to consider the following question: What are the fewest number of indexes with the fewest number of columns I need to achieve my response timing goals while not crippling myself during data INSERTs. The only way to know that for certain is to test, test, and retest using your data and your query loads. If I get queries that frequently hit columns b or b and c or b,c and a then I would consider making an index over (b,c,a). Would I also create indexes over just C and A? That depends on how often they appear alone in the "normal" query load and how responsive you need those queries to be. There is a good thumbrule in IT that relates to many aspects of what we do. It's the 80-20 rule. It applies to so many things. Development: you will spend 20% of your time building a system that meets 80% of your design goals compared to the time it takes to meet 100% of your design goals. Indexes: Compared to the number of indexes it would take to optimize all classes of queries, you should only need 20% of the indexes to cover 80% of the query load. Users: 20% of your users will create 80% (or more) of your support calls and development issues (headaches). 80-20 just fits so many things. Shoot for optimizing just the top 80% of your queries (as determined by their frequency of use) and you should be golden. After you do, keep an eye on the slow query log and your feedback channels and if you see a common pattern, tweak an index you already have or build just what you need to cover that class of query. I very rarely create an index to support a single, infrequently run query. Generally, if the user understands that they are asking for a lot of effort from the database, they will be willing to wait for a response. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
