I'm trying to build out a mysql database design for a project. The problem is coming up with the best solution. Basically in my application, I will have to insert approximately 10-30 rows per user. The primary key will be a random CHAR(16) string. There will also be an datetime index, and an additional row (with an index) called "data".
Day to day, there will only be a heavy amount of inserts and lookups on the table. The lookups will always joined based on the primary key (so joining those 10-30 rows per user).
I will at times need to be able to look at a few specific months (or a full year even) and use mysql GROUP BY functions on the "data" index as well.
At its current volume and estimates, I would expect the table to grow 9.3m rows/month. I do expect this to increase.
So my question comes down to this: mysql partitions, programmatic table separation, or another solution? and are things best separated by month or year? We are running on RHEL, so getting mysql 5.1 may be a bit of work, but if that's a better solution it may be worth going for.
innoDB has already been selected for this project. Day to day performance is the primary concern.