Why doesn't MySQL use an index when I include a subquery?

Question

I have the following query, which is good, but will be slower as the brand table grows:

mysql> explain select brand_id as id,brands.name from tags
    ->                        INNER JOIN brands on tags.brand_id = brands.id
    ->                        where brand_id in
    ->                        (select brand_id from tags where outfit_id in
    ->                        (1,6,68,265,271))
    ->                        group by brand_id, brands.name
    ->                        ORDER BY count(brand_id)
    ->                        LIMIT 5;
+----+--------------------+--------+----------------+------------------------------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type        | table  | type           | possible_keys                                  | key                    | key_len | ref             | rows | Extra                                        |
+----+--------------------+--------+----------------+------------------------------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
|  1 | PRIMARY            | brands | ALL            | PRIMARY                                        | NULL                   | NULL    | NULL            |  165 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | tags   | ref            | index_tags_on_brand_id                         | index_tags_on_brand_id | 5       | waywn.brands.id |    1 | Using where; Using index                     |
|  2 | DEPENDENT SUBQUERY | tags   | index_subquery | index_tags_on_outfit_id,index_tags_on_brand_id | index_tags_on_brand_id | 5       | func            |    1 | Using where                                  |
+----+--------------------+--------+----------------+------------------------------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

I do not understand why it does not use the primary key as an index here and does the file sorting. If I replaced the subquery with the values ​​returned from this subquery, MySQL uses indexes correctly:

mysql> explain select brand_id as id,brands.name from tags
    ->                       INNER JOIN brands on tags.brand_id = brands.id
    ->                       where brand_id in
    ->                       (2, 2, 9, 10, 40, 32, 9, 118)
    ->                       group by brand_id, brands.name
    ->                       ORDER BY count(brand_id)
    ->                       LIMIT 5;
+----+-------------+--------+-------+------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys          | key                    | key_len | ref             | rows | Extra                                        |
+----+-------------+--------+-------+------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
|  1 | SIMPLE      | brands | range | PRIMARY                | PRIMARY                | 4       | NULL            |    6 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | tags   | ref   | index_tags_on_brand_id | index_tags_on_brand_id | 5       | waywn.brands.id |    1 | Using where; Using index                     |
+----+-------------+--------+-------+------------------------+------------------------+---------+-----------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select brand_id from tags where outfit_id in                        (1,6,68,265,271);
+----+-------------+-------+-------+-------------------------+-------------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys           | key                     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+-------------------------+-------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | tags  | range | index_tags_on_outfit_id | index_tags_on_outfit_id | 5       | NULL |    8 | Using where |
+----+-------------+-------+-------+-------------------------+-------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Why was that? It makes no sense to me. I mean, I can break it down into 2 calls, but that seems poor. I noticed that I can make it a little more efficient by including a report in the subquery, but this did not change the way keys are used at all.

Answer 1

Why don't you write:

SELECT brand_id as id,brands.name 
FROM tags
INNER JOIN brands ON tags.brand_id = brands.id
WHERE outfit_id in (1,6,68,265,271)
GROUP BY brand_id, brands.name
ORDER BY count(brand_id)
LIMIT 5;