How Partitioned Tables Supercharge MySQL Performance and Their Limits

We first create a partitioned table employees_partition with 32 partitions on the job_no column and insert 100 million rows using a stored procedure. After the data load we add an index on the name column.

DROP TABLE if exists employees_partition;
CREATE TABLE if not exists `employees_partition` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL COMMENT '员工姓名',
  `job_no` varchar(16) NOT NULL COMMENT '员工工号',
  UNIQUE key(job_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (job_no) PARTITIONS 32;

DROP PROCEDURE IF EXISTS insertemployees;
CREATE PROCEDURE insertemployees()
BEGIN
  DECLARE i INT;
  SET i=1;
  WHILE(i<=100000000) DO
    INSERT INTO employees_partition VALUES(i, CONCAT(i,'-NAME'), CONCAT('NO.',i));
    SET i=i+1;
  END WHILE;
END;

CALL insertemployees();

We then execute three typical queries, each with SQL_NO_CACHE to avoid caching, and examine their execution plans.

Query with partition key : The optimizer selects a specific partition (e.g., p24) and uses a const access type, resulting in extremely fast execution (see image).

Query using secondary index (idx_name) : The plan uses the secondary index but cannot prune partitions, so it scans all 32 partitions; performance is still good but slower than the partition‑key query (see image).

Query without partition key or index : The optimizer performs a full‑table scan across all partitions, taking about 39 seconds (see image).

Note: secondary‑index performance heavily depends on column selectivity; the example uses a highly selective column, so results are fast. With low‑selectivity columns (e.g., status with few distinct values) performance would degrade, similar to a full‑table scan.

For comparison, the same query on a non‑partitioned table with 100 M rows also takes over 30 seconds:

mysql> SELECT SQL_NO_CACHE * FROM employees_nopartition WHERE `id`='8989898';
+---------+--------------+------------+
| id      | name         | job_no     |
+---------+--------------+------------+
| 8989898 | 8989898-NAME | NO.8989898 |
+---------+--------------+------------+
1 row in set, 1 warning (30.78 sec)

Partition Table Limitations

Maximum number of partitions : 8192 (including sub‑partitions) for non‑NDB engines.

No query cache : Queries on partitioned tables automatically disable the query cache.

InnoDB partitions do not support foreign keys .

Full‑text indexes are not supported on partitioned tables.

Spatial columns (POINT, GEOMETRY) cannot be used in partitioned tables.

Temporary and log tables cannot be partitioned.

Unsupported operators : Bitwise operators (|, &, ^, <<, >>) and many built‑in functions are not allowed in partition expressions.

Partition key data type : Must be integer or integer‑compatible; ENUM cannot be used unless using KEY partitioning, and RANGE/LIST COLUMNS can use STRING, DATE, DATETIME.

WINDOW system does not support DATA DIRECTORY / INDEX DIRECTORY for partitions.

Single‑instance resource limits : Connections, I/O, swap, file descriptors, etc., constrain partitioned tables.

When to Use Partitioned Tables

Partitioned tables are ideal when:

Estimated data volume stays within the billions (e.g., < 10 billion rows) over the table’s lifecycle.

Workload does not require extremely high concurrency; user base is limited rather than a massive flash‑sale scenario.

In such cases, partitioning offers lower development and maintenance cost compared to sharding, while still delivering significant performance gains for queries that include the partition key or an efficient index.

Example estimation: a table with 20 k existing rows growing 40 k per day (~1.5 M per year). With 128 partitions (each holding up to 10 k rows), the table can store up to 12.8 billion rows, supporting many years of growth even under various compound‑growth rates.

Understanding each technology’s strengths and limitations allows you to choose the most cost‑effective solution for your specific business scenario.