Most common questions used to investigate
Are you partitioning on columns that are part of all of your unique and primary keys? (In other words if you have a primary key id column, and a createdDate column in a table, you cannot partition on the createdDate because it is not in the primary key.)
Will you have many queries that will end up going through more than 10 partitions?
Do you want a PARTITION table to optimize DELETE statements?
Can you have a single PARTITION that can be dropped every so often, instead of deleting records one by one? Like in a time series or log table where you are deleting the oldest data
Do you want a PARTITION table to optimize queries? (PARTITIONS aren't so good at speeding queries up but let's see.)
Will each of your queries fall within just one or two partitions?
Is you database around the size of your RAM? Are you trying to shrink your memory usage a little bit?
Common conclusions
You cannot use partition on keys that aren't in all of your unique indices. Either change your partition column, or your table, or try a MERGE table or use a VIEW to merge your tables.
PARTITIONS might slow you down. Unless you really need it, or can find a way to make your queries a bit more local to a few PARTITIONS, this might not be a match.
PARTITION is a good option for quickly deleting data. Go for it!
Unless you can organize your partitions so that you can DROP one PARTITION instead of deleting records one by one, PARTITIONS won't help much.
Give it a try, this could save a little bit of RAM and maybe make it faster. It could also go the other way.
I'm guessing it won't help much but it can't hurt to experiment. Tell me if I'm wrong.
This might not be a good match for you.
References
mysql.com is an excellent reference.
Related Problems
