While looking at partitioning I recently made a mistake which I guess can happen to others. Often this is due to not fully reading the documentation or scanning it too quickly and misunderstanding what’s being said.
So this post is to complain about the MySQL partitioning syntax and to warn others who may easily make the same mistake without realising.
First we probably need to ask why we are partitioning a table in the first place. The main reasons for this are I think:
- to improve query performance
- to reduce individual .ibd file sizes for large tables (if using innodb_file_per_table)
In my case I wanted to do both. I had a several tables which store a large number of rows (batches of data) based on an incremental batch number. One of these tables was around 40 GB and had about 500,000,000 rows in it. When processing data in this table often all the data from a particular batch …[Read more]