The mysql select with in clause does not use the index

I have a contacts table which has a primary key of id. It also has a secondary index idx_id_del_user (id, deleted, user_id).

The following query uses the index and therefore is very fast -

select id
from jts_contacts
where id = '00000402-25c8-7375-e3df-4ec5b66de11d'
and deleted = 0;

1 row fetched in 0.0098s

However when I use the in clause the outer query goes into a full table scan. I am expecting it to use either the primary key or the idx_id_del_user.

select  *
from jts_contacts FORCE INDEX (idx_id_del_user)
where id in
(select id
from jts_contacts
where id = '00000402-25c8-7375-e3df-4ec5b66de11d')
and deleted = 0

1 row fetched in 9s

Explain plan -

id, select_type,          table,         type, possible_keys,                 key, key_len, ref, rows, Extra
------------------------------------------------------------------------------------
1, 'PRIMARY',            'jts_contacts', 'ALL', '',                           '',   '',     '', 1127275, 'Using where'
2, 'DEPENDENT SUBQUERY', 'jts_contacts', 'const', 'PRIMARY,idx_id_del_user', 'PRIMARY', '108', 'const', 1, 'Using index'

This table has 1.2 million records and the table was analyzed. I have tried it without the FORCE INDEX option, but it is still not using the index. Any suggestions on making this query faster?

---

Caveat: using a join instead of the in clause will work, however since this is a generated query from a existing product - it cannot be changed to use joins.

---

From what I can tell, IN goes through all of the matching records and compares them to the values in the clause, one row at a time.

So, the best you can do is use an index on deleted and you'll only be going through the records where deleted = 0.