Master MySQL Scheduled Events and Table Partitioning: A Step‑by‑Step Guide

1. Overview of MySQL Scheduled Events

MySQL scheduled events allow you to run SQL statements automatically at defined intervals, which is useful for periodic data updates or generating summary tables.

2. Enabling the Event Scheduler

Check whether the scheduler is active: SHOW VARIABLES LIKE 'event_scheduler'; If the result shows OFF, enable it (the change takes effect after the next server restart):

SET GLOBAL event_scheduler = 1;

3. Event Creation Syntax

The basic form of an event is:

CREATE EVENT event_name ON SCHEDULE schedule_expression DO sql_statement;

Typical schedules include:

Every second: ON SCHEDULE EVERY 1 SECOND Execute after 10 days: ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 DAY Execute at a fixed timestamp: ON SCHEDULE AT TIMESTAMP '2016-08-16 00:00:00' Daily at 03:00, starting the next day: ON SCHEDULE EVERY 1 DAY STARTS '2016-05-18 03:00:00' Daily for 5 days, then stop:

ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY

Start after 5 days, stop after one month:

ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH

4. Advanced Event Usage

Execute multiple SQL statements (separate them with a semicolon).

Temporarily disable an event: ALTER EVENT smudge_insert DISABLE; Re‑enable it: ALTER EVENT smudge_insert ENABLE; Delete an event:

DROP EVENT smudge_insert;

5. Why Partition Large Tables?

Tables exceeding ten million rows are considered large‑data scenarios. Partitioning improves query performance and maintenance by dividing a logical table into independent physical segments.

6. Partitioning Strategies

Horizontal (sharding) : Split rows into multiple tables (e.g., user1, user2). Not recommended because it requires query rewrites.

Vertical : Separate columns into different tables (e.g., user_base, user_extend) and join them when needed.

Table Partitioning : Keep a single logical table but store data in separate files based on a partition key.

7. Partition Types

RANGE : Divide rows by numeric or date ranges (e.g., salary ranges).

HASH : Compute a hash on one or more columns and distribute rows evenly.

KEY : Similar to HASH but uses MySQL‑generated hash values.

LIST : Assign rows to partitions based on a predefined list of values.

8. RANGE Partition Example (salary)

Create a table partitioned by salary intervals:

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT
) PARTITION BY RANGE (salary) (
  PARTITION p0 VALUES LESS THAN (1000),
  PARTITION p1 VALUES LESS THAN (3000),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

Images in the original article illustrate the partition definition and the resulting files.

9. Common Errors

The partition column must be part of the primary key. If it is not, add a primary key on the column before creating the partition.

10. Verifying Partition Files

Navigate to MySQL’s data directory (e.g., /usr/local/mysql/var) and list the files. After inserting test data, use watch -n1 ls -lh to monitor file‑size changes.

11. LIST Partition Example (area_id)

Partition a table into two regions based on area_id:

CREATE TABLE region_data (
  id INT,
  area_id INT,
  ...
) PARTITION BY LIST (area_id) (
  PARTITION p_north VALUES IN (1,2,3),
  PARTITION p_south VALUES IN (4,5,6)
);

12. HASH Partition Example (id)

Distribute rows evenly across four partitions using the primary key:

CREATE TABLE shop (
  id INT PRIMARY KEY,
  ...
) PARTITION BY HASH (id) PARTITIONS 4;

13. KEY Partition Example

Similar to HASH but uses MySQL‑generated hash keys:

CREATE TABLE shop_key (
  id INT,
  ...
) PARTITION BY KEY (id) PARTITIONS 4;

14. Partitioning InnoDB Tables

By default InnoDB uses a shared tablespace ( ibdata1), which cannot be partitioned. Enable per‑table tablespaces by adding the line innodb_file_per_table=1 to my.cnf, restart MySQL, and then create partitions as shown above.

15. Verifying InnoDB Partitions

After enabling per‑table tablespaces and adding partitions, list the table files again; you should see separate .ibd files for each partition, confirming that partitioning succeeded.