Understanding the unique_subquery optimization
If you use the EXPLAIN SELECT statement to see how your subqueries are treated by MySQL, you may sometimes meet the "unique_subquery" optimization. Here is how the manual describes it: " unique_subquery : this type replaces ref for some IN subqueries of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency". Few weeks ago, while I was reviewing a patch fixing a bug in unique_subquery, I got a "simplification" pulsion. I told myself that: unique_subquery is an optimization for a special case of simple subqueries (single inner table, using index, no aggregates); we have a more general system around, used for more complex subqueries, naturally capable of handling simple ones too if we wanted; this general system does not have the bug in question... Then I wondered: what if we removed the unique_subquery optimizat