Technical blog: MySQL indexes on subqueries

Thursday, June 17, 2010

MySQL indexes on subqueries

I was surprised when I got a problem that MySQL doesn't honor indexes when subquery used in IN clause! Problematic query was:

SELECT A, B FROM TABLE_1 WHERE A IN (SELECT A FROM TABLE_2)

If TABLE_1 has index on A then it won't be used by outer query. Problem is caused by missed subquery optimization in MySQL 5.x, outer query won't use indexes if subquery is used:

Thus there are two ways to fix indices issue: 1. use JOIN instead of subselect where it's possible, i.e.

SELECT A, B FROM TABLE_1 t1 INNER JOIN TABLE_2 t2 ON t1.A=t2.A

2. use EXISTS for subqueries, i.e.

SELECT A, B FROM TABLE_1 t1 WHERE EXISTS 
(SELECT 1 FROM TABLE_2 t2  WHERE t1.A=t2.A)

In both approaches we force to use index on A from TABLE_1. These described features are really performed tweaks and they work well in production for us.

Article from MySQL Reference Manual about IN subqueries optimization