我正在尝试优化一个用于电子商务定制商店的mysql查询,该查询有一些内部联接和分组依据,这使得查询非常缓慢,我们正在使用它为类别、供应商、颜色等生成动态过滤器/组合。
有人知道如何优化它吗?首先,我无法使products(p(表使用多索引而不是主键,查询大约需要150-200ms。
确切的查询:
SELECT DISTINCT(sl.id_supplier),
count(DISTINCT p.id_product) as product_count,
sl.supplier_name,
sl.supplier_slug,
sl.supplier_link
FROM supplier s
INNER JOIN supplier_lang sl
on s.id_supplier = sl.id_supplier
AND sl.id_project = 6
AND sl.id_lang =2
AND s.id_project = 6
AND s.active = 1
INNER JOIN product p
ON p.id_project = 6
and p.active = 1
AND p.id_supplier = sl.id_supplier
INNER JOIN product_category pc
on pc.id_project = 6
and pc.id_category = 6
and p.id_product = pc.id_product
GROUP by sl.id_supplier
ORDER BY sl.supplier_name ASC
谢谢
https://snag.gy/F6dy4r.jpg
虽然上面有一个隐含的函数依赖项,但我认为这样的查询看起来更像这样是一个很好的做法(通常也会期望(:
SELECT sl.id_supplier
, sl.supplier_name
, sl.supplier_slug
, sl.supplier_link
, COUNT(DISTINCT p.id_product) product_count
FROM supplier s
JOIN supplier_lang sl
ON sl.id_supplier = s.id_supplier
AND sl.id_project = s.id_project
AND sl.id_lang = 2
JOIN product p
ON p.id_supplier = sl.id_supplier
AND p.id_project = s.id_project
AND p.active = 1
JOIN product_category pc
ON pc.id_product = p.id_product
AND pc.id_project = p.id_project
AND pc.id_category = 6
WHERE s.id_project = 6
AND s.active = 1
GROUP
BY sl.id_supplier
, sl.supplier_name
, sl.supplier_slug
, sl.supplier_link
ORDER
BY sl.supplier_name ASC
为了帮助优化这个查询,我们需要查看所有相关表的SHOW CREATE TABLE语句以及上面的EXPLAIN。这将以编辑您的问题的形式,并且不包括任何图片。