Wide Indexes
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 date, for salesperson, grouped by product_id and referral_id. My index for this table would be: (sale_date, salesperson_id, product_id, referral_id) 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? 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...) Any insight is appreciated. Thanks, --jeff P.S. -- um, sql sql sql -- I can't believe how strict the filter for this list is... - 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
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 date, for 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
Re: Wide Indexes
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 date, for 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
