这是我的问题，它是慢。。。我在寻找优化的方法。

SELECT p.id, 
       Group_concat(pc.cat_id)   AS groups, 
       p.code, 
       p.NAME, 
       p.price, 
       p.thumbnail, 
       p.image, 
       mc.queries                AS merch_queries, 
       mc.position               AS merch_position, 
       Group_concat(op.image)    AS option_images, 
       cf_RETAIL.value           AS custom_RETAIL, 
       cf_rating.value           AS custom_rating, 
       cf_reviews.value          AS custom_reviews, 
       cf_sku.value              AS custom_sku, 
       cf_brand.value            AS custom_brand, 
       cf_custom_thumbnail.value AS custom_custom_thumbnail 
FROM   s01_products AS p 
       LEFT JOIN s01_categoryxproduct AS pc 
              ON p.id = pc.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'RETAIL') AS cf_RETAIL 
              ON p.id = cf_RETAIL.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'rating') AS cf_rating 
              ON p.id = cf_rating.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'reviews') AS cf_reviews 
              ON p.id = cf_reviews.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'sku') AS cf_sku 
              ON p.id = cf_sku.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'brand') AS cf_brand 
              ON p.id = cf_brand.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'custom_thumbnail') AS cf_custom_thumbnail 
              ON p.id = cf_custom_thumbnail.product_id 
       LEFT JOIN (SELECT p.product_id          AS product_id, 
                         Group_concat(q.query) AS queries, 
                         Min(p.position)       AS position 
                  FROM   s01_srch_merchandisingproduct AS p 
                         LEFT JOIN s01_srch_merchandisingquery AS q 
                                ON q.id = p.query_id 
                  GROUP  BY p.product_id) AS mc 
              ON p.id = mc.product_id 
       LEFT JOIN s01_options AS op 
              ON p.id = op.product_id 
                 AND op.image <> '' 
WHERE  p.active = 1 
GROUP  BY p.id

谢谢你的帮助！
更新的表架构：

**s01_categoryxproduct**

cat_id, 
product_id, 
disp_order

**s01_products**

id      
catcount    
agrpcount   
pgrpcount   
disp_order  
code            
name            
thumbnail       
image           
price       
cost        
descrip         
weight      
taxable     
active      
sku         
cancat_id   
page_id     
page_title      
dt_created  
dt_updated

**s01_CFM_ProdValues**

field_id, 
product_id, 
value, 
value_long

**s01_CFM_ProdFields**

id, 
code, 
name, 
group_id, 
fieldtype, 
info, 
facet

**s01_Options**

id, 
product_id, 
attr_id, 
disp_order, 
code, 
prompt, 
price, 
cost, 
weight, 
image

**s01_SRCH_MerchandisingProduct**

id, 
product_id, 
query_id, 
position

**s01_SRCH_MerchandisingQuery**

id, 
query