SELECT DISTINCT pc.products_id, pd.products_name,p.products_date_added,pso.products_id
FROM products_to_categories AS pc
LEFT JOIN products_description AS pd ON pd.products_id=pc.products_id
LEFT JOIN products AS p ON p.products_id=pd.products_id
LEFT JOIN specials AS sps ON sps.products_id=p.products_id
LEFT JOIN temp_products_7days_orders_amount AS 7days ON 7days.products_id=pc.products_id
LEFT JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model
LEFT JOIN products_stockout AS pso ON pso.products_id=pd.products_id
WHERE p.products_status=1 AND (prq.msg != 'Temporary out stock.' OR ISNULL(prq.msg))
AND pc.categories_id IN ( 153,323,1055,1241,1431)
AND MATCH(pd.products_name) AGAINST('*iphone*' IN BOOLEAN MODE) AND MATCH(pd.products_name)
AGAINST('*c*' IN BOOLEAN MODE) ORDER BY 7days.orders_sum DESC
SELECT DISTINCT pc.products_id, pd.products_name,p.products_date_added,pso.products_id
FROM products_to_categories AS pc
LEFT JOIN products_description AS pd FORCE INDEX(PRIMARY) ON pd.products_id=pc.products_id
LEFT JOIN products AS p ON p.products_id=pd.products_id
LEFT JOIN specials AS sps ON sps.products_id=p.products_id
LEFT JOIN temp_products_7days_orders_amount AS 7days ON 7days.products_id=pc.products_id
LEFT JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model
LEFT JOIN products_stockout AS pso ON pso.products_id=pd.products_id
WHERE p.products_status=1 AND (prq.msg != 'Temporary out stock.' OR ISNULL(prq.msg))
AND pc.categories_id IN ( 153,323,1055,1241,1431)
AND MATCH(pd.products_name) AGAINST('*iphone*' IN BOOLEAN MODE) AND MATCH(pd.products_name)
AGAINST('*c*' IN BOOLEAN MODE) ORDER BY 7days.orders_sum DESC
explain如下
+----+-------------+-------+--------+-----------------------+-------------------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+-------------------+---------+---------------------------+------+----------------------------------------------+
| 1 | SIMPLE | pc | range | PRIMARY,categories_id | categories_id | 4 | NULL | 307 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | banggood.pc.products_id | 1 | Using where |
| 1 | SIMPLE | pd | ref | PRIMARY | PRIMARY | 4 | banggood.p.products_id | 764 | Using where |
| 1 | SIMPLE | sps | ref | products_id | products_id | 4 | banggood.pc.products_id | 16 | Using index |
| 1 | SIMPLE | 7days | ref | PRIMARY | PRIMARY | 4 | banggood.p.products_id | 1032 | |
| 1 | SIMPLE | prq | ref | ix_prg_sku_or_poa | ix_prg_sku_or_poa | 152 | banggood.p.products_model | 10 | Using where |
| 1 | SIMPLE | pso | eq_ref | PRIMARY | PRIMARY | 4 | banggood.pd.products_id | 1 | Using index |
+----+-------------+-------+--------+-----------------------+-------------------+---------+---------------------------+------+----------------------------------------------+