select b.brand_id, b.brand_name, COUNT(*) AS goods_num from `anang`.`ecs_goods` g
left join `anang`.`ecs_brand` b on g.brand_id=b.brand_id
where g.cat_id IN ('47')
AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND g.is_audit=1
GROUP BY g.brand_id数据表中有将近40条记录,查询太过缓慢,直接在数据库执行都要七八秒。请教高手如何优化
left join `anang`.`ecs_brand` b on g.brand_id=b.brand_id
where g.cat_id IN ('47')
AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND g.is_audit=1
GROUP BY g.brand_id数据表中有将近40条记录,查询太过缓慢,直接在数据库执行都要七八秒。请教高手如何优化
ecs_brand用到的字段建联合索引
AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND g.is_audit=1
这几个列尝试建一个索引 尽可能多的缩小数据查询范围比如在cat_id上建立索引 或者加别的区分度比较大的列一起做联合索引
show index from ..以供分析。
还有count(*) 一般改成count(1)会快些
或者用一下代码试试。SELECT
b.brand_id, b.brand_name, COUNT(1) AS goods_num
FROM
`anang`.`ecs_brand` b
right join
(
SELECT
brand_id
FROM
`anang`.`ecs_goods` g
WHERE
g.cat_id ='47'
AND g.is_on_sale = 1
AND g.is_alone_sale = 1
AND g.is_delete = 0
AND g.is_audit=1
)g1
WHERE
g1.brand_id=b.brand_id
GROUP BY g1.brand_id