MySQL Forums
Forum List  »  Optimizer & Parser

Re: Use of DISTINCT in INNER JOIN
Posted by: Steve Croall
Date: April 01, 2009 03:36AM

Here are the EXPLAINs for the SELECTs without the DISTINCT keyword:

mysql> EXPLAIN SELECT a.col1
-> FROM tab1 a INNER JOIN tab2 b
-> ON a.col1 = b.col1
-> WHERE b.col2 in ('value1', 'value2')
-> ORDER BY a.col1
-> ;
+----+-------------+-------+-------+-----------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+----------------+------+--------------------------+
| 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using index |
| 1 | SIMPLE | b | ref | PRIMARY,ix_tab2 | PRIMARY | 4 | brmuser.a.col1 | 1 | Using where; Using index |
+----+-------------+-------+-------+-----------------+---------+---------+----------------+------+--------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> EXPLAIN SELECT a.col1, a.col2
-> FROM tab1 a INNER JOIN tab2 b
-> ON a.col1 = b.col1
-> WHERE b.col2 in ('value1', 'value2')
-> ORDER BY a.col1
-> ;
+----+-------------+-------+-------+-----------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+----------------+------+--------------------------+
| 1 | SIMPLE | a | index | PRIMARY | PRIMARY | 4 | NULL | 1 | |
| 1 | SIMPLE | b | ref | PRIMARY,ix_tab2 | PRIMARY | 4 | brmuser.a.col1 | 1 | Using where; Using index |
+----+-------------+-------+-------+-----------------+---------+---------+----------------+------+--------------------------+
2 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
66640
March 24, 2009 12:07PM
10887
March 24, 2009 02:00PM
6723
March 25, 2009 07:01PM
6430
March 27, 2009 05:20AM
5632
March 27, 2009 10:01AM
6602
March 28, 2009 02:02AM
4504
March 28, 2009 03:53PM
Re: Use of DISTINCT in INNER JOIN
5695
April 01, 2009 03:36AM
5968
April 02, 2009 12:16AM
5439
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.