How to Effectively Use Partition Tables in Multi-Table Joins to Improve Query Performance

Based on previous introductions to MySQL partition tables, this article explores whether partition tables can improve performance in multi‑table join scenarios and explains why they sometimes make queries slower.

Scenario 1: Two tables joined on the partition key without any filter condition

Using partitioned tables in this case worsens performance because the optimizer must consider many partitions, increasing the number of tables involved in the join.

localhost:ytt>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 1000 */

localhost:ytt>show create table t1_no_pt\G
*************************** 1. row ***************************
       Table: t1_no_pt
Create Table: CREATE TABLE `t1_no_pt` (
  `id` int DEFAULT NULL,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `log_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Join performance results:

select count(*) from t1_no_pt a inner join t1 b using(id);  -- 6.76 sec
select count(*) from t1 a inner join t1 b using(id);        -- 4.32 sec
select count(*) from t1_no_pt a inner join t1_no_pt b using(id); -- 0.87 sec

Scenario 2: Two tables joined on the partition key with a filter condition

2.1 Filter on the partition key (equality)

When the filter condition is an equality on the partition key, the optimizer can prune to a single partition, yielding significant speedups.

select count(*) from t1 a inner join t1 b using(id) where a.id = 19172; -- 0.01 sec
select count(*) from t1_no_pt a inner join t1_no_pt b using(id) where a.id = 19172; -- 0.55 sec
select count(*) from t1 a inner join t1_no_pt b using(id) where a.id = 19172; -- 0.32 sec

Explain plan shows much lower cost for the partitioned tables.

EXPLAIN format=tree select count(*) from t1 a inner join t1 b using(id) where a.id = 19172\G
-- cost=381.90 rows=280 (partitioned)
EXPLAIN format=tree select count(*) from t1_no_pt a inner join t1_no_pt b using(id) where a.id = 19172\G
-- cost=249264389.78 rows=249125777 (non‑partitioned)

2.2 Filter on a non‑partition column

If the filter does not involve the partition key, partitioned tables can be dramatically slower.

select count(*) from t1 a inner join t1 b using(id) where a.r1 = 10; -- 6.16 sec
select count(*) from t1_no_pt a inner join t1_no_pt b using(id) where a.r1 = 10; -- 0.70 sec

Scenario 3: Join key is not the partition key, but filter condition is the partition key

Performance remains poor for two partitioned tables, but mixing a partitioned table with a regular table can improve speed.

select count(*) from t1 a inner join t1 b using(r1) where a.id = 19172; -- 6.05 sec (partitioned)
select count(*) from t1_no_pt a inner join t1_no_pt b using(r1) where a.id = 19172; -- 0.54 sec (non‑partitioned)
select count(*) from (select * from t1 a where a.id = 19172) t inner join t1_no_pt b using(r1); -- 0.39 sec (mixed)

Scenario 4: Two partitioned tables with different partitioning algorithms or partition counts

When partition counts differ, joins become slower than using non‑partitioned tables.

select count(*) from t1 a inner join t2 b using(id); -- 6.43 sec (different partition counts)
select count(*) from t1_no_pt a inner join t2_no_pt b using(id); -- 1.98 sec (non‑partitioned)

Summary of when to use partition tables for joins

The partition key must be the join condition.

If the partition key is not the join condition, the filter condition must be the partition key.

When joining two partitioned tables, they must use the same partitioning method and have the same number of partitions.