MySQL Forums
Forum List  »  Partitioning

Re: How should I partition this table?
Posted by: Rick James
Date: April 27, 2013 09:58PM

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

In my opinion, that is totally useless.

> `UPC` varchar(14) DEFAULT NULL,
> DEFAULT CHARSET=utf8

Will UPC every be anything other than ascii? It is (in some cases) better to use ascii or latin1 for non-utf8 strings.

> `ComSplDate` date DEFAULT NULL,
> `ComSplTime` time DEFAULT NULL,

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

> `ItemCost` double DEFAULT NULL,

Don't use FLOAT or DOUBLE for money; use DECIMAL.

> 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.

Back to PARTITIONing...

What are the queries? -- Picking the PARTITION key based whether it will benefit the SELECTs.

Will you 'purge' data that is 'old'? -- If so, the partition only RANGE(TO_DAYS(invDate)) and don't have more than a few dozen partitions; no subpartitions. This will facilitate purging.

More notes:
http://mysql.rjweb.org/doc.php/ricksrots#partitioning

Options: ReplyQuote


Subject
Views
Written By
Posted
2928
April 26, 2013 09:18AM
Re: How should I partition this table?
1543
April 27, 2013 09:58PM


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.