Database Design

Question

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.

Answer 1

This doesn't answer your question, but it needs to be mentioned...

The primary key will be a random CHAR(16) string.

This is a Bad Idea. Use an UNSIGNED BIGINT column with AUTO_INCREMENT. No need to reinvent the wheel: you won't have to worry about key management or collisions that way.

Answer 2

Partition the data on the dates (and maybe additionally the user it is per-user data and you have lots of users).

Then create a monthly table with the SUM, COUNT, AVG, etc that you need and the appropriate group by. You can partition that table as well (but dates probably won't be a meaningful partition)

Then create a yearly table like the monthly table.

Populate the monthly and yearly tables with REPLACE INTO ... SELECT ... statements.