Re: Optimising query with self-join and GROUP BY

[Björn Steinbrink]

Having indices on (id, segment_id) and (segment_id, id) should avoid the temporary table and the filesort. But the query proves to be still way too slow with my artificial testdata.

Here's a version that works quite well for me and only requires an index on (segment_id, id)

SELECT
    a.segment_id
FROM 
    (SELECT DISTINCT segment_id FROM current_way_segments WHERE id = 1) a
LEFT JOIN
    current_way_segments b
ON 
    b.segment_id = a.segment_id AND 
    b.id != 1
WHERE 
    b.segment_id IS NULL