What is type index_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 an indexed column.

Examples

✅ When it is 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,
);

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

The optimization is applied because the subquery returns an indexed column (medications.substance). As you see, the outer query doesn't need an index on the column prescriptions.xxx for the optimization to be applied - only the subquery is essential.

❌ When it is not used

CREATE TABLE books(
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    author varchar(100) NOT NULL,
    INDEX(author)
);
CREATE TABLE professors(
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    faculty varchar(100) NOT NULL,
    INDEX(faculty)
);

SELECT * FROM books WHERE author IN(
    SELECT name FROM professors WHERE faculty = 'Cultural Studies'
);

The index_subquery strategy will not be used because the returned column of the subquery (professors.name) is not an indexed column.

How to optimize a query using `index_subquery`?

MySQL already applied a very specific optimization for your part of the query. It's not guaranteed that you can really optimize this further. But you can try one of these rewrites that your query will be executed entirely different:

Change the subquery to return either the primary or a unique key
Replace the subquery with a join

Conclusion

The strategy MySQL selected is optimized specifically for your use case and how you used the subquery. It's a common occurrence with MySQL queries so you can be pretty sure that MySQL did everything it could to optimize the query. The only optimization step remaining for you is rewriting the query by entirely removing the subquery.