MySQL Forums
Forum List  »  Optimizer & Parser

Re: Use of DISTINCT in INNER JOIN
Posted by: Rick James
Date: March 27, 2009 10:01AM

It's not DISTINCT that causes the issue, it is the need for extra columns in the 'other' table.

SELECT  DISTINCT a.col1, a.col2       -- note: a.col2, not b.col2
    FROM  a
    INNER JOIN  b ON a.col1 = b.col1        -- not joining on col2
    WHERE  b.col2 in ('value1', 'value2')   -- b.col2; no clue of a.col2
    ORDER BY  a.col1 
INDEX (col1)       -- a
INDEX (col1, col2) -- b

MySQL is not very good at optimizing IN and OR. So, it probably did not see much opportunity in using b's INDEX (col1,col2). Instead it decided to do a table scan on one table, then reach into the other table (nested loop join). At that point, picking a vs b was possibly a toss up. Actually, it may have deliberately done a, then b -- note that it needs to use INDEX(col1,col2) to get into b, at which point it might have check "b.col2 IN(...)". I can't tell from the EXPLAIN.

Options: ReplyQuote


Subject
Views
Written By
Posted
66784
March 24, 2009 12:07PM
10898
March 24, 2009 02:00PM
6726
March 25, 2009 07:01PM
6438
March 27, 2009 05:20AM
Re: Use of DISTINCT in INNER JOIN
5638
March 27, 2009 10:01AM
6611
March 28, 2009 02:02AM
4509
March 28, 2009 03:53PM
5702
April 01, 2009 03:36AM
5979
April 02, 2009 12:16AM
5445
April 02, 2009 06:29AM


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.