Mysql select with in article does not use index

Question

I have a contact table that has an id primary key. It also has a secondary index idx_id_del_user (id, deleted, user_id).

The following query uses an index and therefore very fast -

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

1 row extracted in 0.0098s

However, when I use the in clause, the outer query goes into a full table scan. I expect it to use either the primary key or 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 received in 9s

Explain the 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 contains 1.2 million records and the table was analyzed. I tried it without the FORCE INDEX option, but it still does not use the index. Any suggestions on speeding up this query?

---

Caution: using a connection instead of an in clause will work, however, since this is a generated query from an existing product, it cannot be changed to use joins.

Answer 1

From what I can say, IN goes through all the relevant entries and compares them with the values ​​in the sentence, one line at a time.

So, the best thing you can do is use the index on deleted , and you will go through the entries where deleted = 0 .

Answer 2

I had the same problem, and after reading the Marcus Adams entry, I thought about what I learned about starting UPDATE in a table based on data from the same table: create a view first, for example:

 SELECT * FROM jts_contacts WHERE id IN (SELECT id FROM (SELECT id FROM jts_contacts WHERE id = '00000402-25c8-7375-e3df-4ec5b66de11d') temp) AND deleted = 0 

First you get the full result from the internal query into the temp view, and then run the external query based on this.

This template increased the speed of my similar requests from minutes to less than a second. I do not love you.