Chris, We run a large data warehouse with tables similar to yours. We basically gave up on indexing and the overhead involved and just tablescan. The key is to partition the data using a concept called Merge Tables. However, since we currently use Oracle, eager to migrate to MySQL - I don't have concrete MySQL stats on this concept.
Theoretically speaking - here's the anticipated math if you were to try and set this up on MySQL Merge Tables (I made some assumptions about your table). 120,000,000 rows * 400 fields * 5 bytes/field average = 250 GB. Using our server as an example - we read at 800 MB/s so worse case you can read the entire table in 5 minutes. However, if you partition your data by ZIP (which appears in all but 1 of your queries) you would only read those relevant slices, vastly reducing your run time. The one query which doesn't have zip has STATE so you can have a ZIP/STATE conversion table that generates a list of zips in leu of state and modify the query to use it - and again enjoy the benefits of partitioning. If most of your queries use zip and most of them go after a small slice of the table you will probably be under 1 minute response time most of the time. Worse case would be 5 minutes. No indexes to maintain on insert, or rebuild from scratch. Udi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
