MySQL table with automatic partition adding

One of the advantages of using partitions in MySQL is that it speeds up queries, especially when large data volumes are in play. What happens when data grows and you surely don’t want to add new partitions manually? Well, this is where stored procedures and triggers come in place.

First things first, you cannot partition tables which are not InnoDB (storage engine). Partition types are various and can be: RANGE, LIST, HASH, KEY. For more information on which type to use please visit this link

Ok! So now let’s take a real life example. Let’s say I have a table that stores Call Records information from a SIP service. The volumes will be huge of course and thus partitioning is a must in order to speed up searches later on. Let’s create our table that will hold in one column the date of the call.

Creating the table

CREATE TABLE cdrs(
  id INT AUTO_INCREMENT PRIMARY KEY,
  date_column DATE,
  -- Other columns
);

CREATING THE Stored procedure

Given the expected rows my choice of partitioning is by Month. This of course can be tweaked to your needs. Let’s create the stored procedure that adds a new partition to the table each time we store a new CDR in a new month.

DELIMITER $$

CREATE PROCEDURE add_partition(IN new_partition_date DATE)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE cursor c CURSOR FOR
    SELECT MAX(date_column) FROM cdrs;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  DECLARE CONTINUE HANDLER FOR EXIT HANDLER SET @max_date = done;

  OPEN c;

  read_loop: LOOP
    FETCH c INTO @date;

    IF @date > new_partition_date THEN
      LEAVE read_loop;
    END IF;

    IF @date < new_partition_date THEN
      UPDATE your_table
      SET @date = @date + INTERVAL 1 MONTH;
    END IF;

    IF done THEN
      LEAVE read_loop;
    END IF;
  END LOOP;

  CLOSE c;
END$$

DELIMITER ;

Note that the table here is cdrs hence change it to your table name.

Create the trigger

Create an AFTER INSERT trigger that calls the add_partition stored procedure with the date of the new row’s date column.

DELIMITER $$

CREATE TRIGGER after_insert_trigger
AFTER INSERT
ON your_table
FOR EACH ROW
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE cursor c CURSOR FOR
    SELECT date_column FROM cdrsWHERE date_column > NEW.date_column;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  DECLARE CONTINUE HANDLER FOR EXIT HANDLER SET @max_date = done;

  OPEN c;

  read_loop: LOOP
    FETCH c INTO @date;

    IF @date > NEW.date_column THEN
      UPDATE your_table
      SET @date = @date + INTERVAL 1 MONTH;
    END IF;

    IF done THEN
      LEAVE read_loop;
    END IF;
  END LOOP;

  CLOSE c;
END$$

DELIMITER ;

Note that the table here is cdrs hence change it to your table name.

Call the stored procedure

After inserting a new row into the table, call the add_partition stored procedure with the date of the new row’s date column as an argument.

CALL add_partition(DATE('2023-01-01'));

Conclusion

By following these steps, you can create a table in MySQL that automatically adds a new partition with each new month. This approach will help you maintain an efficient and organized database structure while accommodating the changing data range.