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多秒,请教优化的写法,谢谢
解决方案 »
- 关于MYSQL两表大量数据查询比对的问题。请教高手。
- 将MYSQL数据库中的表内容转移到ORACLE数据库中 在线等
- 同一个表内 UPDATE SELECT COUNT 排序的问题
- mysql存储过程
- mysql sql语句整数做过除法之后的排序出了问题?
- .net连接mysql的问题
- mysql administrator tool 在设置指定时间自动备份后不运行?
- 求mysql中出现中文表,字段,记录时的处理(VC下)
- 关于安装mysql后的启动问题。50分相送
- 按uidList顺序如何批量得到返回按照原有顺序得到UserList
- mysql5.6 如何永久的改变character_set_server为gbk?
- mysql 2张表去除重复数据
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