56,912
社区成员




SELECT COUNT(*) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_special ps ON (p.product_id = ps.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '107' OR p2c.category_id = '77' OR p2c.category_id = '75' OR p2c.category_id = '74' OR p2c.category_id = '71' OR p2c.category_id = '70' OR p2c.category_id = '69' OR p2c.category_id = '7');
+----+--------------------+-------+----------------+--------------------------------+------------+---------+----------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+----------------------------------+------+--------------------------+
| 1 | PRIMARY | p2s | index | PRIMARY,store_id | PRIMARY | 8 | NULL | 7360 | Using where; Using index |
| 1 | PRIMARY | p | eq_ref | PRIMARY,date_available | PRIMARY | 4 | fashione_db.p2s.product_id | 1 | Using where |
| 1 | PRIMARY | ps | ref | product_id | product_id | 4 | fashione_db.p.product_id | 1 | Using index |
| 1 | PRIMARY | pd | eq_ref | PRIMARY | PRIMARY | 8 | fashione_db.p2s.product_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p2c | index_subquery | PRIMARY,category_id,product_id | product_id | 4 | func | 1 | Using where |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+----------------------------------+------+--------------------------+
5 rows in set (0.00 sec)
explain select count(*) total from product p left join product_description pd on (p.product_id = pd.product_id) where pd.language_id = 1 and p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '218' or p2c.category_id = '220');
+----+--------------------+-------+----------------+--------------------------------+------------+---------+--------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+--------------------------------+------+--------------------------+
| 1 | PRIMARY | p | index | PRIMARY | PRIMARY | 4 | NULL | 9771 | Using where; Using index |
| 1 | PRIMARY | pd | eq_ref | PRIMARY | PRIMARY | 8 | fashione_db.p.product_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p2c | index_subquery | PRIMARY,category_id,product_id | product_id | 4 | func | 1 | Using where |
+----+--------------------+-------+----------------+--------------------------------+------------+---------+--------------------------------+------+--------------------------+