Lets say I have a table with 100M rows. All table have enough space on buffer pool. So is there any performance or others benefit to create partition on that table?

NB: Storage engine - InnoDB

1 Answer 1


If your purge strategy includes time-specific purge eligiblity, partitions could be helpful. For example, if every quarter, you will purge everything over 5 years old, it will be in your best interest to have a quarterly data table plan in place. Taking a table out of service is quicker/cleaner than DELeting a quarter's worth of data rows. innodb_file_per_table=ON will be needed to be effective. Filenames need to represent the datacontent, such as 2018Q1, 2018Q2 etc or possibly 2018W01 - W53 if you want calendar weeks to be able to purge. Dream big, plan carefully.

For alternative to simple filenaming strategy as above,

Read more on: http://mysql.rjweb.org/doc.php/partitionmaint


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.