原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的就不要用子查询