MySQL Table Partitioning for Large WordPress Databases

MySQL table partitioning splits a large table into physical segments while keeping it logically unified. For WordPress sites with millions of posts, partitioning wp_posts by year can dramatically reduce the rows scanned in date-range queries.

Problem: The wp_postmeta table on a high-traffic WooCommerce site grows to tens of millions of rows — even with proper indexes, range queries over large date windows become slow as the table reaches hundreds of millions of rows.

Solution: Use MySQL table partitioning to split wp_postmeta (or a custom analytics table) by date range using PARTITION BY RANGE (YEAR(...)). Queries that include a partition key in the WHERE clause benefit from partition pruning, scanning only the relevant subset of rows.

The examples below show how to convert an existing wp_posts table to a RANGE partition by year, check partition statistics, and prune old partitions safely.

-- NOTE: Test on a staging copy first. Partitioning ALTER TABLE locks the table.
-- wp_posts uses a BIGINT auto-increment primary key — we must partition by a key
-- that is part of the primary key or by a generated column.

-- Step 1: Add a computed year column (MySQL 5.7.6+ generated columns)
ALTER TABLE wp_posts
  ADD COLUMN post_year SMALLINT UNSIGNED GENERATED ALWAYS AS (YEAR(post_date)) STORED,
  ADD INDEX idx_post_year (post_year);

-- Step 2: Partition by RANGE on the year column
-- This requires the partition key to be part of the primary key.
-- Re-create the primary key to include post_year:
ALTER TABLE wp_posts
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (ID, post_year);

-- Step 3: Apply RANGE partitioning
ALTER TABLE wp_posts
PARTITION BY RANGE (post_year) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

Inspect partition statistics and add new partitions each year:

-- Inspect partition row counts and sizes
SELECT
  partition_name,
  table_rows,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.PARTITIONS
WHERE table_schema = 'helloadmin'
  AND table_name = 'ynYYAa7bFT_posts';

-- Confirm the optimizer uses partition pruning:
EXPLAIN SELECT ID, post_title FROM wp_posts
WHERE post_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND post_status = 'publish';
-- Look for 'partitions: p2023' in the EXPLAIN output

-- Add a partition for 2025 (reorganise the catch-all partition)
ALTER TABLE wp_posts REORGANIZE PARTITION p_future INTO (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Remove (drop) an old partition — DATA IS DELETED
ALTER TABLE wp_posts DROP PARTITION p2020;

-- Remove partitioning entirely (merge all partitions back)
ALTER TABLE wp_posts REMOVE PARTITIONING;

NOTE: Partitioning is not a substitute for indexes — always have appropriate indexes within each partition. Also note that foreign keys are not supported on partitioned tables, which is fine for WordPress since it uses no foreign keys by default.