Posts Tagged ‘partitioning’

MySQL Database Maintenance Stored Procedure Update

This is just a quick update of a stored procedure to assist with MySQL Database Maintenance. I originally posted this back in 2012. The Stored Procedure allows you to run OPTIMIZE TABLE or ANALYZE TABLE on all (or most) tables in a MySQL database. In this version I’ve added some simple logging and automatic recognition of partitioned tables to take advantage […]


MySQL table Partitioning can be used in various way to improve performance. I wanted to get some idea of how this would affect database maintenance operations like OPTIMIZE TABLE. For these tests I used a default install of MySQL 5.6.15, with a key_buffer_size of 2GB, and executed OPTIMIZE TABLE table_name after a series of actions […]

Purging data & Partitioning for Paupers

Several months ago at work we started having some terrible problems with some jobs that purge old data from our system. These jobs were put into place before my time, and while fine at the time, were now causing us some big problems. Purging data would take hours and cause horrendous blocking while they were […]

For RANGE partitions each partition must be defined

If you encounter the following error when trying to create a partitioned table in MySQL Error Code : 1492 For RANGE partitions each partition must be defined Assuming you have defined your partitions then you probably have a syntax error. Take the following incorrect example. CREATE TABLE People ( PersonId INTEGER NOT NULL AUTO_INCREMENT, FirstName […]