MySQL Partitioning

Question

I have a MyISAM table that holds a few million Messages.

I am thinking about converting it to an partition InnoDB table.

Just wondering about the best partition strategy.

I would like to do this without a code change, and the two indexes I think would be most relevant are

• ID a bigint (primary key)
• SENT_DATE datetime (index)
• FROM_ADDRESS varchar (index)

The table is mostly used for - looking up things based on date range - by a specific id - most recent row with matching FROM_ADDRESS

Is it possible to do this with SEND_DATE? I have read that partitioning needs to be done on an integer that is the primary key, is this correct?

If I partition this by id, say batches of 250000 and then search for a SENT_DATE/FROM_ADDRESS will pruning work? or will it still have to check all partitions to find it?

Thanks in advance for any help!

Answer 1

Personally I'd use the date as your partition function, and partition by a hash of the year and month. Maybe splitting the data into 48 or more partitions. I've done this on some large volume databases and had good results.

ALTER TABLE `your_table` 
PARTITION BY HASH(YEAR(`date_field`)*12 + MONTH(`date_field`)) 
  PARTITIONS 48;

This should create a nice distributed set of data across 48 partitions (you may need to fiddle with the calculation on the date to get it quite right for your needs).

I build a model in Excel, with all the dates down one column, put the partition function on the second showing which partition that data would appear in. You can then chart the second column frequency to see how the data distribution is placed across the partitions - a really useful way of tinkering with your function before you alter your table!

Hope that helps...

Answer 2

PARTITION BY HASH is probably useless for ranges. Do PARTITION BY RANGE, and write a script to prune the oldest partition (if desired) and add a new partition.

Please provide SHOW CREATE TABLE and the important SELECTs -- we need details of your situation in order to give detailed advice.