Hmmm... Ok, my original assumption was that if I was grouping by those fields in my SELECT, then including them in the index would help -- if the order of my GROUP BY followed the order of the fields in the index.
Your saying it doesn't make much of a difference whether I include them or not? Thanks, --jeff ----- Original Message ----- From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Jeff Kilbride" <[EMAIL PROTECTED]> Cc: "MySQL" <[EMAIL PROTECTED]> Sent: Thursday, February 21, 2002 11:19 PM Subject: Re: Wide Indexes > 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
