Using MySQL Partitioned Tables Without Primary Keys: Performance Implications and Index Design

MySQL requires the partition key to be a primary key or a subset of it, but if a table has no primary key this restriction does not apply, allowing any column to serve as the partition key.

Two example tables, p1 (partitioned by c1 ) and p2 (partitioned by c2 ), are created without explicit primary keys:

<mysql:8.0.31:(ytt)>create table p1(c1 int,c2 int,c3 int) partition by hash(c1) partitions 10;
Query OK, 0 rows affected (0.22 sec)
<mysql:8.0.31:(ytt)>create table p2(c1 int,c2 int,c3 int) partition by hash(c2) partitions 10;
Query OK, 0 rows affected (0.18 sec)

Retrieving the 800,001st row from p1 requires a full table scan, which is slow (0.27 s). Using a sub‑query with row_number() is even slower (1.85 s). Both approaches suffer from poor performance because the table lacks a primary key.

<mysql:8.0.31:(ytt)>select * from p1 where 1 limit 800000,1;
... (0.27 sec)
<mysql:8.0.31:(ytt)>select c1,c2,c3 from (select *,row_number() over() as sn from p1) T where sn=800001;
... (1.85 sec)

Creating a comparable table p3 with an auto‑increment primary key and the same partition key yields a much faster lookup (0.00 s) when querying by the primary key.

<mysql:8.0.31:(ytt)>create table p3(id int auto_increment,c1 int,c2 int,c3 int,primary key(id,c1)) partition by hash(c1) partitions 10;
Query OK, 0 rows affected (0.25 sec)
<mysql:8.0.31:(ytt)>insert p3(c1,c2,c3) table p1;
Query OK, 1000000 rows affected (6.61 sec)
<mysql:8.0.31:(ytt)>select c1,c2,c3 from p3 where id = 800001;
... (0.00 sec)

The conclusion is that partitioned tables without primary keys are only suitable for small data volumes and infrequent queries.

Index Design for Partitioned Tables Without Primary Keys

When a query includes the partition key (e.g., a HASH partition on c1 ), the partition key itself already narrows the search to a single partition, so the index does not need to contain the partition key.

<mysql:8.0.31:(ytt)>select count(*) from p1 where c1 = 10 and c2 =10;
... (0.04 sec)

Adding an index on the non‑partition column c2 improves the same query to virtually zero time.

<mysql:8.0.31:(ytt)>alter table p1_copy add key idx_c2(c2);
Query OK, 0 rows affected (6.26 sec)
<mysql:8.0.31:(ytt)>select count(*) from p1_copy where c1 = 10 and c2 =10;
... (0.00 sec)

For queries that do not contain the partition key, adding the partition key to an index provides no benefit because all partitions must still be scanned.

Therefore, for partitioned tables without primary keys, the partition key should generally be omitted from index definitions after weighing the trade‑offs.