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!

2 Answers 2


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`)) 

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

  • Nice choice to partition by HASH. +1 !!! Nov 21, 2011 at 16:47
  • @RolandoMySQLDBA, will MySQL use partition pruning on HASH partitions?
    – Stoleg
    Jun 5, 2013 at 14:57
  • 1
    @Stoleg yes MySQL will use partition pruning on HASH partitions, but this places a great limitation on you for queries. Why? Because only queries that look like this SELECT ... WHERE col='value' are of any good to a HASH partition schema. Range queries could be a little daunting in terms of pruning because of having to determine if each row's hash value has a corresponding partition in existence. Please see dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html (starting from For tables that are partitioned by HASH or KEY) for further details. Jun 5, 2013 at 15:17
  • True, but for my particular case we were only interested in single months of data at a time. You could however expand your range to an "IN ()" clause, listing all the relevant months would help there too.
    – Dave Rix
    Aug 22, 2013 at 9:33

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.