I have a table with millions of records, lots of fields, and lots of indexes. It appears I break every rule for table efficiency...but there is a reason! I needed to do summarized data on *lots* of the fields at once, and the savings in time & simplicity far outweigh any performance hits I may have come up against (which are few).
However, I have a question on the best design for an index. Let's say I have three fields in particular I usually summarize the dataset with: "Date", "Country" and "CustomerType". Typically I would always query the table with a specific "Date" in mind (in YYYY-MM-DD format), but often I would also supply Country and/or CustomerType. Within a specific date range, Country would have less than a dozen variants, while CustomerType may have less. So a couple sample queries would be: "Select Country,Sum(sales) From MyTable Where (Date>'2002-01-01' and Date<'2002-09-01') Group By Country" "Select CustomerType,Sum(sales) From MyTable Where (Date>'2002-01-01' and Date<'2002-09-01') Group By CustomerType" "Select CustomerType,Sum(sales) From MyTable Where (Date>'2002-01-01' and Date<'2002-09-01') And Country='Canada' Group By CustomerType" If I understand the MySQL documentation correctly (and I probably don't!), the most efficient index would be to have indexes with Country and CustomerType listed first, since their values number far less than Date. This would mean indexes of: Date,Country Date,CustomerType Country,Date CustomerType,Date Country,CustomerType,Date CustomerType,Country,Date That seems like a lot to me. If I simply made Date the first entry on all it would be: Date,Country,CustomerType Date,CustomerType,Country But, as I said, would having the date first be efficient? Any thoughts would be appreciated! Aaron Fransen http://gomail.dynu.org --------------------------------------------------------------------- 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
