High Performance MySQL [Ch.7: Advanced MySQL Features]

Partitioned tables

A partitioned table is one logical table stored as several physical ones. Each partition has its own handler underneath the server; a thin partitioning layer routes every statement to the right partitions, so the storage engine just sees ordinary tables. Every index on the table exists separately in each partition, and all partitions use the same engine.

It helps in a few specific situations:

• Huge tables with a hot end. Recent rows are read constantly and old ones rarely; partitions keep the hot part small.
• Cheap bulk deletes. Dropping a whole partition of last year's data is a metadata operation, not millions of row deletes.
• Per-partition maintenance: rebuild, check or restore one slice instead of the whole table.

How statements are routed

Locking inside a partition follows normal InnoDB rules (row locks, not partition locks).

Common schemes are RANGE (by date or id ranges, the usual choice for time series), LIST (explicit value sets), and HASH or KEY (spread rows evenly to reduce hot spots):

CREATE TABLE bug_events (
  id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

MySQL requires the partitioning columns to be part of every unique key, which is why created_at joins the primary key above.

What goes wrong with partitions

• Queries that do not filter on the key visit every partition, searching each partition's copy of the index. That can be slower than no partitioning at all.
• NULL partitioning values land in the first partition, so lookups may have to check it in addition to the expected one. Keep the expression non-nullable.
• Too many partitions make routing and opening tables costly. Dozens are fine; thousands are a smell.
• Pruning needs a recognizable predicate. Filter on the column directly (created_at >= '2025-01-01') and check the partitions column of EXPLAIN to confirm only the expected partitions are listed.

Views

A view is a named query; it stores no data. MySQL runs it one of two ways. With the MERGE algorithm, the view's definition is folded into your query, so filters combine and indexes still apply; this is the efficient path. With TEMPTABLE, the view's result is materialized into a temporary table first and your query reads that. TEMPTABLE is forced whenever the view uses GROUP BY, DISTINCT, aggregates, UNION or certain subqueries, and it means filters from outside cannot reach the base table's indexes.

Simple views can be updatable, and WITH CHECK OPTION rejects writes through the view that would produce rows the view itself would not show.

Views rarely make queries faster. Their practical value is access control and simplicity: expose a view without the sensitive columns and grant SELECT on the view only. MySQL has no materialized views; the cache and summary tables from chapter 4, refreshed by a job or an event, are the substitute.

Triggers

Triggers run code BEFORE or AFTER an INSERT, UPDATE or DELETE, inside the same transaction as the statement. That atomicity makes them handy for keeping denormalized columns or summary counters in sync without extra round trips. MySQL 8 allows several triggers per event and table.

The costs are real:

• Row by row. MySQL triggers are always FOR EACH ROW; a statement touching a million rows fires a million times.
• Invisible work. A one-line statement can do a lot behind the scenes, which skews profiling and surprises the next developer.
• Locks and failures. Triggers can cause unexpected lock waits and deadlocks, and their errors fail the original statement with a message that points nowhere obvious.

Events

Events are scheduled SQL, a cron that lives in the server: run once at a given time or on an interval. Keep the event body tiny and put the logic in a stored procedure. MySQL does not stop a new run from starting while the previous one is still going, so guard long jobs with a named lock:

CREATE EVENT purge_old_bug_events
ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
    IF GET_LOCK('purge_old_bug_events', 0) THEN
      CALL purge_old_bug_events();
      DO RELEASE_LOCK('purge_old_bug_events');
    END IF;
  END

The event scheduler must be enabled (event_scheduler=ON, the default in MySQL 8), and events only run on the server where they are defined, which matters with replicas.

Where this series stops

The rest of the book moves from the query to the machine: server settings, the operating system and hardware, replication, scaling, high availability, backup and recovery. Those matter, but they are mostly operations work, and managed MySQL services handle much of it for application teams. Chapters 1 to 7 cover what you control from the query editor.

Common questions