Why MySQL Subqueries Can Kill Performance and How Joins Save the Day

While reviewing server metrics, an alert indicated unusually high load; checking MySQL showed CPU usage at 2000% with no I/O wait, suggesting a problematic query.

Running show full processlist revealed hundreds of connections executing the same subquery, prompting a deeper analysis.

The involved tables have simple structures:

mysql> desc t1;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| role_id | int(11)     | NO   |     | 0       |                |
| referer | varchar(20) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

mysql> desc t2;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| role_id      | int(11) | NO   | MUL | 0       |                |
| privilege_id | int(11) | NO   |     | 0       |                |
+--------------+---------+------+-----+---------+----------------+

Indexes are defined as:

mysql> show index from t1;
+-------+------------+----------+--------------+-----------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-----------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    | 0          | PRIMARY  | 1            | id        | A         | 329         | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-----------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show index from t2;
+-------+------------+----------+--------------+-----------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-----------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    | 0          | PRIMARY  | 1            | id        | A         | 12826       | NULL     | NULL   |      | BTREE      |         |               |
| t2    | 1          | role_id  | 1            | role_id   | A         | 583         | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-----------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The problematic query:

mysql> select privilege_id from t2 where role_id in (select role_id from t1 where id=193);

EXPLAIN shows a full table scan on t2:

mysql> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows  | Extra    |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------+
| 1  | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL | 12826 | Using where |
| 2  | DEPENDENT SUBQUERY | t1    | const| PRIMARY       | PRIMARY| 4     | const| 1     |          |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------+

Replacing the subquery with a JOIN:

mysql> explain select a.privilege_id from t2 as a inner join t1 as b on a.role_id=b.role_id and b.id=193;
+----+--------+-------+------+---------------+------+---------+------+-----+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows| Extra |
+----+--------+-------+------+---------------+------+---------+------+-----+-------+
| 1  | SIMPLE | b     | const| PRIMARY       | PRIMARY| 4     | const| 1   |       |
| 1  | SIMPLE | a     | ref  | role_id       | role_id| 4    | const| 128 |       |
+----+--------+-------+------+---------------+------+---------+------+-----+-------+

Even using an equality subquery enables index usage:

mysql> explain select privilege_id from t2 where role_id = (select role_id from t1 where id=193);
+----+----------+-------+------+---------------+------+---------+------+-----+----------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows| Extra    |
+----+----------+-------+------+---------------+------+---------+------+-----+----------+
| 1  | PRIMARY   | t2    | ref  | role_id       | role_id| 4     | const| 128 | Using where |
| 2  | SUBQUERY  | t1    | const| PRIMARY       | PRIMARY| 4     | NULL | 1   |          |
+----+----------+-------+------+---------------+------+---------+------+-----+----------+

The root cause is an optimizer limitation in MySQL 5.5: subqueries in the IN clause require the inner query to be based on a unique index or primary key, which older versions mishandle. MySQL 5.6 and later fix this behavior, as shown by the different EXPLAIN outputs.

After converting the logic to a JOIN, the server load gradually decreased, as confirmed by monitoring graphs.

Conclusion: Although subqueries are convenient, they can severely degrade performance in MySQL versions prior to 5.6; using JOINs is generally safer and more efficient.