Announcement

Announcement Module
Collapse
No announcement yet.

Anyway to avoid temp table for this GROUP BY?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Anyway to avoid temp table for this GROUP BY?

    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_ta gs_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 | | +----+-------------+---------------+--------+--------------------------------------------------------------------------+-------------------------+---------+----------------------------------------------+-------+-----------------------------------------------------------+

  • #2
    Can you please post SHOW CREATE TABLE for vehicles and tags_vehicles tables?

    Comment

    Working...
    X