Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing.

There is an index on search_id column in search_criteria table and on pricing_id column in pricing table.

But running this nested query does not use index on search_criteria table.

explain 
select *  
from  search_criteria USE INDEX (idx_search_id) 
where  search_id in 
    (select search_id 
    from pricing
     where pricing_id = '009330be-d041-444f-a624-ca652f3f61ed');

+----+--------------------+---------------------+------+------------------------------+----------------+---------+-------+----------+-------------+
| id | select_type        | table               | type | possible_keys                | key            | key_len | ref   | rows     | Extra       |
+----+--------------------+---------------------+------+------------------------------+----------------+---------+-------+----------+-------------+
|  1 | PRIMARY            | search_criteria | ALL  | NULL                         | NULL           | NULL    | NULL  | 19582252 | Using where |
|  2 | DEPENDENT SUBQUERY | pricing      | ref  | idx_pricing_id,idx_search_id | idx_pricing_id | 36      | const |        1 | Using where |
+----+--------------------+---------------------+------+------------------------------+----------------+---------+-------+----------+-------------+

If I use this table without the nested query it uses the index

```
explain extended select * from search_criteria where search_id in ('36afabcc-e896-48b6-ad0f-c683845d4a4f')

+----+-------------+---------------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | search_criteria | ref | idx_search_id | idx_search_id | 103 | const |

You are trying to execute a semijoin. This is a very well know issue with the MySQL optimizer before MySQL 5.6. The only way MySQL knows how to execute it is to perform a full table scan on the left table and execute the inner query once per row, thus it is unable to use the index.

You have several alternatives:

• Migrate to 5.6 (or MariaDB 5.5): it will be optimized correctly (I think in this case, converted to a JOIN transparently)

-
Rewrite the query as a scalar subquery (this only works if you expect to receive only one value, that is, pricing_id is unique):

explain 
select *  
from search_criteria USE INDEX (idx_search_id) 
where search_id = 
  (select search_id 
   from pricing
   where pricing_id = '009330be-d041-444f-a624-ca652f3f61ed');

-
Rewrite the query as a JOIN. This only works in some semijoins (those where you will not match more than one row at a time), but I think this is the case for you:

explain 
select s_c.*  
from search_criteria s_c USE INDEX (idx_search_id) 
JOIN pricing p USING (search_id)
WHERE p.pricing_id = '009330be-d041-444f-a624-ca652f3f61ed';

By the way, once you have changed the query, you will not need the USE INDEX.

StackExchange Database Administrators Q#84075, answer score: 7