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

In my MySQL database, table events has a composite index with columns closingeventid, timeStart and eventCode. There are more than 21 开发者_StackOverflow中文版million rows in table events.

Here is two SQLs, if I run the first one in MySQL command line, the Mem Usage of process mysqld-nt.exe increases gradually with 10 M per second, it goes up around 1.6 G then plummets to around 30 M. Then the command line returns a Out of Memory exception.

But, it's ok if I use the second SQL, with INNER JOIN instead of IN. It seems IN clause doesn't hit any index.

Why? Any idea to monitor MySQL execution process like execution plan of MS SQL?

SELECT COUNT(*) AS 'cnt' 
FROM events 
WHERE 
     (timeStart < '2010-09-22 14:29:10’) 
 AND (closingeventid IS NULL) 
 AND (eventcode IN (SELECT DISTINCT evtcclosed FROM eventclose))`

VS

SELECT COUNT(1) AS 'cnt' 
FROM events 
inner join 
          (SELECT DISTINCT evtcclosed FROM eventclose) ec 
   ON ec.evtcclosed = events.eventcode 
WHERE (timeStart < '2010-09-22 14:29:10’) AND (closingeventid IS NULL)

---

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 ...

---

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)