On Thu, Feb 21, 2002 at 08:13:51PM -0800, Jeff Kilbride wrote: > > For reporting purposes, I usually use a wide index across all the > fields that are relevant to creating the reports. So, for example, > my sales table has this type of data: > > sale_id > sale_date > salesperson_id > product_id > referral_id > > [other sales data] > > sale_id is an auto_incrementing primary key. Most of the reports I > run are date-based -- show me the sales for xxxx date, for yyyy > salesperson, grouped by product_id and referral_id. My index for > this table would be: > > (sale_date, salesperson_id, product_id, referral_id)
Got it. > My question is this: I know it's not good to index fields that have > few unique values, but what about including a field like that in a > wide index? I asked Monty about this last year and was surprised at his response. It's not nearly as "bad" of an idea as I had thought at the time. > If product_id only has 5 values, does it hurt to include it in this > wide index? Would it be better to include it or leave it out, if I'm > grouping by that field? Is there a rule of thumb for unique-ness > when indexing? (i.e. a column should have X unique values before > using it in an index...) With a 4-field index like that, if you're mainly selected based on sale_date, there are only a few cases when having the product_id and referral_id indexed will really help. Here's one. If your query retrieves ONLY fields appearing in that index, MySQL will be smart enough to never even look at the table--it'll just used the index data directly. Is there another? Probably, but I haven't thought of it... Someone else my chime in. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 14 days, processed 468,981,120 queries (369/sec. avg) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
