MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimising query with self-join and GROUP BY
Posted by: Björn Steinbrink
Date: May 09, 2007 05:21PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimising query with self-join and GROUP BY
3373
May 09, 2007 05:21PM


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.