index not used in subquery where in clause

Question

mysql version is 5.5.40-0+wheezy1-log

I have this query:

SELECT cycle_id, sum(fst_field) + sum(snd_field) AS tot_sum 
FROM mytable WHERE parent_id IN (
  SELECT id FROM mytable WHERE cycle_id = 2662
)

I have these indexes:

• parent_id
• parent_id, cycle_id, fst_field, snd_field

If I execute the command

EXPLAIN EXTENDED SELECT cycle_id, sum(fst_field) + sum(snd_field) AS tot_sum 
FROM mytable WHERE parent_id IN (
  SELECT id FROM mytable WHERE cycle_id = 2662
)

This is the result:

+----+--------------------+-----------+-----------------+----------------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table     | type            | possible_keys        | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-----------+-----------------+----------------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | mytable   | ALL             | NULL                 | NULL    | NULL    | NULL | 185971 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | mytable   | unique_subquery | PRIMARY,cycle_id_idx | PRIMARY | 4       | func |      1 |   100.00 | Using where |
+----+--------------------+-----------+-----------------+----------------------+---------+---------+------+--------+----------+-------------+

It does not use any index. I tried to add other composed indexes (i tried several), without success.

Answer 1

I don't remember if 5.5 still had a very crude handling of IN ( SELECT ... ). If so, that would probably explain the problem

Consider upgrading to 5.6 or 5.7 or 8.0.

Convert the query to use a JOIN.

INDEX(cycle_id) is needed.