Your data model is wrong. You should have data form all days in one table. Why the split into a table per day? You need a better solution to the problem that resulted in per day data split in individual tables.

This is still in devopment so I can change, but I thought I could get faster queries by breaking the data up into daily tables.

Each table will have about 50000 entries and I want to keep 6 months worth of data. Can MySQL handle a table with 9 million rows well?

I agree with Gorilla that your data model is incorrect. There is not really a good way to sum up those values across multiple tables. Plus the number of days in a month vary, so you are going to continue to run into complications when querying your dataset due to the fact that it's spread across all of these daily tables.

I'm a SQL*Server guy, but if MySQL can't handle 9 million rows then a) you're using the wrong DB or b) you might consider trimming back the amount of data you keep or archive off old data monthly to another instance of MySQL.

If you insist on using daily tables as you've described above, you might consider having a summary table with a transaction date, a value for field c3, and a count of how many rows appeared in the daily table for field c3. You could schedule a job to run after midnight to summarize the previous day's data and append the daily results to the summary table. So in the above example one 20080504 the summary table would look like:

TranDate¦Value.¦Count
20080501¦Red...¦2
20080501¦Blue..¦1
20080502¦Green.¦1
20080502¦Red...¦1
20080502¦Blue..¦1
20080503¦Yellow¦1
20080503¦Red...¦2

Now you have summary counts by color by day and everything is in a single table. Using this same table you can get them by week, month, or any date range you'd like by querying one table with a simple query.

[edited by: ZydoSEO at 6:00 pm (utc) on May 11, 2008]

MySQL can handle the number of rows you are looking for, but you will probably want to create one more more indexes to support the queries you need to perform well.

I suggest you create a small script to generate a test dataset with the max number of records you expect. Then play around with queries and observe performance. The explain command should help you understand what indexes you need.

[edited by: Gorilla at 6:13 pm (utc) on May 11, 2008]

OK, I followed Gorilla's advice. I created a 4th column with the data. It was performing well with 10 days worth of data without any indexes. I have also gone ahead and indexed 2 columns and hopefully that will offset any slowness issues.

If the queries start to take too long I may just create temp tables with daily stats.

Thanks for all your help.