如何重组下面的查询以更快地运行?运行需要几分钟时间,这会显著降低Web服务器的速度。查询使用内联查询和联接从多个表中获取产品的详细信息。我有一种感觉,一定有更快的方法。
编写以下查询最有效的方法是什么?
SELECT p.*,
pd.*, pd.NAME AS NAME, pi.*,p.image AS default_image,
(SELECT first FROM product_shipping psi
WHERE psi.product_id = p.product_id) AS flat_rate,
(SELECT NAME FROM manufacturer m
WHERE m.manufacturer_id = p.manufacturer_id) AS manufacturer,
(SELECT price FROM product_special ps
WHERE ps.product_id = p.product_id AND ps.customer_group_id = 8) AS special_price
FROM product p
LEFT JOIN product_description pd
ON ( p.product_id = pd.product_id )
LEFT JOIN product_image pi
ON ( p.product_id = pi.product_id )
ORDER BY p.product_id
如果没有索引,则需要索引。脑海中浮现出以下内容:
product_shipping(product_id, first)
manufacturer(manufacturer_id, name)
product_special(product_id, customer_group_id, price)
product_description(product_id)
product_image(product_id)
product(product_id, manufacturer_id)
如果没有合适的索引,重新安排子查询和联接可能对性能影响不大。
您的选择中有很多项目是重复的,但由于最初是这样获取数据的,所以我保持原样。此外,使用LEFT JOIN
s可能比INNER JOIN
s:慢
SELECT
product.*,
product_description.*,
product_description.NAME AS NAME,
product_image.*,
product.image AS default_image,
product_shipping.first AS flat_rate,
manufacturer.NAME AS manufacturer,
product_special.price AS special_price
FROM
product
LEFT JOIN product_description ON product.product_id = product_description.product_id
LEFT JOIN product_image ON product_image.product_id = product_image.product_id
LEFT JOIN manufacturer ON product.manufacturer_id = manufacturer.id
LEFT JOIN product_special ON product.product_id = product_special.product_id AND product_special.customer_group_id = 8
ORDER BY product.product_id
与@Harper给出的答案不同,我选择了内部联接,因为你是在内联选择上进行的,所以我的建议是:
SELECT p.*,
pd.*, pd.NAME AS NAME, pi.*,p.image AS default_image,
psi.first AS flat_rate,
m.NAME AS manufacturer,
ps.price AS special_price
FROM product p
INNER JOIN product_shipping psi
ON (psi.product_id = p.product_id)
INNER JOIN manufacturer m
ON (m.manufacturer_id = p.manufacturer_id)
INNER JOIN product_special ps
ON (ps.product_id = p.product_id AND ps.customer_group_id = 8)
LEFT JOIN product_description pd
ON ( p.product_id = pd.product_id )
LEFT JOIN product_image pi
ON ( p.product_id = pi.product_id )
ORDER BY p.product_id