MySQL Forums
Forum List  »  Partitioning

Re: How should I partition this table?
Posted by: Ron Bergin
Date: April 28, 2013 11:29AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2957
April 26, 2013 09:18AM
Re: How should I partition this table?
1401
April 28, 2013 11:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.