MySQL:in与join的优化

原sql：

mysql> select count(*) as num from gp as p where p.goods_id in (select goods_id from gg where gg.category_id = 62) and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
+-----+
| num |
+-----+
|  40 |
+-----+
1 row in set (0.70 sec)

mysql> explain select count(*) as num from gp as p where p.goods_id in (select goods_id from gg where gg.category_id = 62) and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
+----+--------------------+----------+-----------------+------------------------------+---------+---------+-------+--------+-------------+
| id | select_type        | table    | type            | possible_keys                | key     | key_len | ref   | rows   | Extra       |
+----+--------------------+----------+-----------------+------------------------------+---------+---------+-------+--------+-------------+
|  1 | PRIMARY            | p        | ref             | is_stop,is_check_2           | is_stop | 1       | const | 134703 | Using where |
|  2 | DEPENDENT SUBQUERY | gg       | unique_subquery | PRIMARY,category_id,goods_id | PRIMARY | 4       | func  |      1 | Using where |
+----+--------------------+----------+-----------------+------------------------------+---------+---------+-------+--------+-------------+
2 rows in set (0.00 sec)

in改为join后：

mysql> select count(*) as num from gp as p,(select goods_id from gg where gg.category_id = 62) g where p.goods_id = g.goods_id  and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
+-----+
| num |
+-----+
|  40 |
+-----+
1 row in set (0.01 sec)

mysql> explain select count(*) as num from gp as p,(select goods_id from gg where gg.category_id = 62) g where p.goods_id = g.goods_id  and p.status=1 AND p.is_check=1 AND p.is_stop=0 AND p.stock > 0  LIMIT 1;
+----+-------------+------------+------+-----------------------------+-------------+---------+------------+------+-------------+
| id | select_type | table      | type | possible_keys               | key         | key_len | ref        | rows | Extra       |
+----+-------------+------------+------+-----------------------------+-------------+---------+------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                        | NULL        | NULL    | NULL       |  116 |             |
|  1 | PRIMARY     | p          | ref  | goods_id,is_stop,is_check_2 | goods_id    | 4       | g.goods_id |    1 | Using where |
|  2 | DERIVED     | gg         | ref  | category_id                 | category_id | 4       |            |  116 | Using index |
+----+-------------+------------+------+-----------------------------+-------------+---------+------------+------+-------------+
3 rows in set (0.00 sec)

MySQL中，可以用join的就不要用子查询