MySQL Forums
Forum List  »  Optimizer & Parser

Optimising query with self-join and GROUP BY
Posted by: Richard Fairhurst
Date: May 07, 2007 01:24PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimising query with self-join and GROUP BY
8553
May 07, 2007 01:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.