What is type unique_subquery in MySQL EXPLAIN?

Performance Classification: good

The unique_subquery strategy is a special and fast optimization within MySQL for col IN(SELECT ... FROM ...) subqueries. Its automatically applied whenever the subquery returns the primary or a unique key.

Examples

✅ When it is used

CREATE TABLE schools (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    INDEX(name)
);
CREATE TABLE teachers (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    school_id bigint NOT NULL,
    name varchar(100) NOT NULL
);

SELECT * FROM teachers WHERE school_id IN(
    SELECT id FROM schools WHERE name = 'MySQL Edu'
);

The strategy is applied because the subquery returns the table's primary key. The outer table (teachers) is not required to have an index on the column used for the condition (school_id).

CREATE TABLE leaks (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dataset varchar(100) NOT NULL,
    email varchar(100) NOT NULL,
    password varchar(100) NOT NULL,
    INDEX(email)
);
CREATE TABLE users (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email varchar(100) NOT NULL UNIQUE,
    company varchar(100) NOT NULL
);

SELECT * FROM leaks WHERE email IN(
    SELECT email FROM users WHERE email like 'marc%@example.com'
)

Any unique key within the subquery can also be returned - it does not have to be the primary key.

❌ When it is not used

CREATE TABLE medications(
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    substance varchar(100) NOT NULL,
    INDEX(substance)
);
CREATE TABLE prescriptions(
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    person varchar(100) NOT NULL,
    substance varchar(100) NOT NULL,
    INDEX(substance)
);

SELECT * FROM prescriptions WHERE substance IN(
    SELECT substance FROM medications WHERE substance = 'acetyl%'
);

The unique_subquery optimization can only be applied when the subquery returns a unique or primary key value. An indexed column (substance) is not sufficient.

How to optimize a query using `unique_subquery`?

There's nothing to optimize. The query already uses a very optimized strategy that can not be applied often. But you can try rewriting the subquery to a join and evaluate whether the performance improved. Typically, MySQL automatically rewrites subqueries to joins but couldn't do it for this query.

Conclusion

You can stop searching whether this part of your query needs to be optimized when you see the unique_subquery type. You can't optimize this subquery any further as it is already executed with MySQL's fastest subquery optimization. The only option you have left is rewriting the subquery with a join - in case the subquery is slow.