I'm curious about if MySQL's
where ... in (...) statement removes duplicates as an optimization.
For example, if I use a subquery, is it important that I use
DISTINCT to remove duplicates?
An example query:
SELECT * FROM foo WHERE bar_id IN ( SELECT id FROM bar where user_id = 4 );
The subquery has the potential to return duplicate
Does MySQL query optimization make this query better?
SELECT * FROM foo WHERE bar_id IN ( SELECT DISTINCT id FROM bar where user_id = 4 );
From my tests on a database with about 2.5mm rows in the
bar table (with indexes where appropriate for such a query), the result time is roughly the same (on a large RDS instance with plenty of overhead for now).
I should note that I'm hoping for an explanation more than "the subquery returns less results when using
DISTINCT, so of course it's better", as that ignores MySQL's query optimizer.
For example, perhaps
DISTINCTuses more resources and is therefore slower overall, especially if a
where...in(...)statement easily optimizes duplicates. These are the details I'm unsure about.