What's the difference between IN and INNER JOIN in my case?

Answer 1

The MySQL optimizer optimizes an IN (subquery) clause very badly. It performs nested query for each row of outer query.

To get the execution plan - just add EXPLAIN keyword right before your query

EXPLAIN SELECT ...

Answer 2

Thanks zerkms!

I tried EXPLAIN on both SQLs, but it seems both of them hit the right index idxEventClosed (closingeventid, timeStart, eventCode). The explaining looks same exception the bold line below. I still don't have much idea about what caused the out of memory issue.

mysql> EXPLAIN SELECT COUNT(*) AS 'cnt' FROM events WHERE (timeStart < '2010-10-29 14:29:10') AND (closingeventid IS NULL) AND (eventcode IN (SELECT D
ISTINCT evtcclosed FROM eventclose));
+----+--------------------+------------+-------+-------------------------+----------------+---------+------+---------+------------------------------+
| id | select_type        | table      | type  | possible_keys           | key            | key_len | ref  | rows    | Extra                        |
+----+--------------------+------------+-------+-------------------------+----------------+---------+------+---------+------------------------------+
|  1 | PRIMARY            | events     | range | idxStart,idxClosedEvent | idxClosedEvent | 17      | NULL | 4335955 | Using where; Using index     |

|  2 | DEPENDENT SUBQUERY | eventclose | ALL   | NULL                    | NULL           | NULL    | NULL |      10 | Using where; Using temporary |

+----+--------------------+------------+-------+-------------------------+----------------+---------+------+---------+------------------------------+
2 rows in set (0.00 sec)



mysql> EXPLAIN SELECT COUNT(1) AS 'cnt' FROM events inner join (SELECT DISTINCT evtcclosed FROM eventclose) ec on ec.evtcclosed = events.eventcode whe
re (events.closingeventid is null) and (timeStart < '2010-10-28 14:29:10');

+----+-------------+------------+-------+---------------------------------+----------------+---------+------+---------+--------------------------+
| id | select_type | table      | type  | possible_keys                   | key            | key_len | ref  | rows    | Extra                    |
+----+-------------+------------+-------+---------------------------------+----------------+---------+------+---------+--------------------------+
|  1 | PRIMARY     | events     | range | evtNLoc,idxStart,idxClosedEvent | idxClosedEvent | 17      | NULL | 4330270 | Using where; Using index |
**|  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL           | NULL    | NULL |       8 | Using where              |**
|  2 | DERIVED     | eventclose | ALL   | NULL                            | NULL           | NULL    | NULL |      10 | Using temporary          |
+----+-------------+------------+-------+---------------------------------+----------------+---------+------+---------+--------------------------+
3 rows in set (0.00 sec)