SELECT g.goods_id, g.goods_name, g.up_nums, g.goods_name_style, g.et_price,
g.goods_brief, g.is_new, g.is_best, g.is_hot, g.att, g.shop_price AS org_price,
g.promote_price, g.goods_type, g.promote_start_date, g.promote_end_date, g.goods_brief,
g.goods_thumb , g.goods_img, g.file_size, g.publishing_company, g.publish_date,
AVG(r.comment_rank) AS comment_rank
FROM `resource`.`res_goods` AS g LEFT JOIN `resource`.`res_comment` AS r
ON r.id_value = g.goods_id
WHERE g.is_delete = 0 AND (( g.cat_id = '32' ) OR g.goods_id = 32 )
GROUP BY g.goods_id ORDER BY goods_id DESC
res_goods 里有3W多条数据,执行了10多秒,请教优化的写法,谢谢
g.goods_brief, g.is_new, g.is_best, g.is_hot, g.att, g.shop_price AS org_price,
g.promote_price, g.goods_type, g.promote_start_date, g.promote_end_date, g.goods_brief,
g.goods_thumb , g.goods_img, g.file_size, g.publishing_company, g.publish_date,
AVG(r.comment_rank) AS comment_rank
FROM `resource`.`res_goods` AS g LEFT JOIN `resource`.`res_comment` AS r
ON r.id_value = g.goods_id
WHERE g.is_delete = 0 AND (( g.cat_id = '32' ) OR g.goods_id = 32 )
GROUP BY g.goods_id ORDER BY goods_id DESC
res_goods 里有3W多条数据,执行了10多秒,请教优化的写法,谢谢
show index from ...
explain select ...以供分析。
SELECT
g.goods_id,
g.goods_name,
g.up_nums,
g.goods_name_style,
g.et_price,
g.goods_brief,
g.is_new,
g.is_best,
g.is_hot,
g.att,
g.shop_price AS org_price,
g.promote_price,
g.goods_type,
g.promote_start_date,
g.promote_end_date,
g.goods_brief,
g.goods_thumb,
g.goods_img,
g.file_size,
g.publishing_company,
g.publish_date,
AVG(r.comment_rank) AS comment_rank
FROM
`resource`.`res_goods` AS g
LEFT JOIN `resource`.`res_comment` AS r ON r.id_value = g.goods_id
WHERE
g.is_delete = 0
AND (
(g.cat_id = '32')
OR g.goods_id = 32
)
GROUP BY
g.goods_id
ORDER BY
goods_id DESC