Optimising query with self-join and GROUP BY

[Richard Fairhurst]

Hello all,

I'm doing some development for OpenStreetMap (www.openstreetmap.org) and trying to optimise a particularly tricky query.

The query looks in a table (current_way_segments) which has 'id' and 'segment_id' columns. It aims to find those values of segment_id which coincide with only a given value of id (#{way}). In other words, given this data set:

segment_id,id
1,1
2,1
3,1
1,2
4,2

and running the query with #{way}=1, it would return segment_ids 2 and 3; it wouldn't return segment_id 1, because this is also present for id 2.

The query is:

SELECT a.segment_id,COUNT(a.segment_id) AS ct
FROM current_way_segments AS a, current_way_segments AS b
WHERE a.segment_id=b.segment_id
AND a.id=#{way}
GROUP BY a.segment_id
HAVING ct=1

Because (I'm guessing) of the GROUP BY, however, it uses filesort and a temporary table, which slows it down. Here's the output from EXPLAIN:

+----+-------------+-------+------+---------------------------------------------------------------------+------------------------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------------------------------------+------------------------------+---------+----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | a | ref | current_way_segments_seg_idx,current_way_segments_id_idx,segment_id | current_way_segments_id_idx | 9 | const | 84 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | ref | current_way_segments_seg_idx,segment_id | current_way_segments_seg_idx | 9 | openstreetmap.a.segment_id | 1 | Using where; Using index |
+----+-------------+-------+------+---------------------------------------------------------------------+------------------------------+---------+----------------------------+------+----------------------------------------------+

Any suggestions as to how I could improve this? Thanks in advance.

Richard