Re: Use of DISTINCT in INNER JOIN

[Steve Croall]

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)