Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
    
Nice choice to partition by HASH. +1 !!! –  RolandoMySQLDBA Nov 21 '11 at 16:47
    
@RolandoMySQLDBA, will MySQL use partition pruning on HASH partitions? –  Stoleg Jun 5 '13 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. –  RolandoMySQLDBA Jun 5 '13 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 '13 at 9:33
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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