Hi,
I have a vehicles and tags table. The following query returns vehicle_ids which have certain tags_ids. Is it possible to avoid the temp table? The query is currently taking ~400ms against 40k vehicles.
EXPLAIN SELECT id FROM vehicles, tags_vehiclesWHERE tags_vehicles.vehicle_id = vehicles.idAND tags_vehicles.tag_id IN (10,92,6,26) GROUP BY vehicles.id HAVING COUNT(vehicles.id) = 4 ORDER BY vehicles.renewed_on DESC LIMIT 0, 52
±—±------------±--------------±-------±-------------------------------------------------------------------------±------------------------±--------±---------------------------------------------±------±----------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±--------------±-------±-------------------------------------------------------------------------±------------------------±--------±---------------------------------------------±------±----------------------------------------------------------+| 1 | SIMPLE | tags_vehicles | range | ix_vehicle_id_tag_id,fk_tags_vehicles_tag_id,fk_tags_vehicles_vehicle_id | fk_tags_vehicles_tag_id | 4 | NULL | 40644 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | vehicles | eq_ref | PRIMARY | PRIMARY | 4 | carlist_development.tags_vehicles.vehicle_id | 1 | | ±—±------------±--------------±-------±-------------------------------------------------------------------------±------------------------±--------±---------------------------------------------±------±----------------------------------------------------------+