Re: How should I partition this table?

[Ron Bergin]

Hello Rick,

I'm off for the next 2 days, so I won't be able to answer some of your questions until I get back to the office on Tue.

>> PARTITION BY HASH (YEAR(invdate)) PARTITIONS 10

> In my opinion, that is totally useless.

I had the same opinon when I saw that and is why I thought about partitioning on the division field. We currently have 36 divisions.

> Do not split DATE and TIME, unless you have some good reason. Use DATETIME instead.

I take it you're suggesting that I alter the table and combine those fields. I'll need to check with our POS programmers, but I suspect that doing so will break lots of their code. FYI, I'm in operations dept and don't have direct access to the code that acesses/uses this db.

>> so all of the tablespace data is in ibdata1.

> Oops, that makes it hard to return disk space to the OS when you change the partitioning, etc.

I've copied the db to a test server and enabled innodb_file_per_table on it which should, I think, fix that issue. The db on the test box is about 40gb smaller than the production.

> Will you 'purge' data that is 'old'?

I've been told that we will "never" purge any data. The oldest record in this db is 2005. Records older than that are still being kept in our pervasive db.

> SUBPARTITIONs are not useful.

Why is that? I didn't see anything in your "Rules of Thumb" that gave a clear reason to that point.