browneyedgurl
New Member
I have the following query, which is fine, but will get slower as the brands table grows:\[code\]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)\[/code\]I don't understand why it isn't using the primary key as the index here and doing a file sort. If I replace the subquery with the values returned from that subquery, MySQL correctly uses the indices:\[code\]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)\[/code\]Why would this be? It doesn't really make sense to me. I mean, I can break it up into 2 calls, but that seems poor. I did notice that I can make it slightly more efficient by including a distinct in the subquery, but that didn't change the way it uses keys at all.