mysqli连接查询很慢

Question

我的网站有存储结果的嵌套查询，随着我的代码变大，最终给我带来了问题。我正在尝试学习如何连接表和执行子查询，以便简化我的代码。此查询返回我需要的确切信息，但添加此查询后我的页面加载时间从 1 秒变为 9 秒。有什么方法可以加快速度，还是我遗漏了一些东西，导致需要这么长时间？

SELECT p.product_id, 
       p.product_name, 
       p.product_pic, 
       AVG(r.review_stars), 
       COUNT(DISTINCT r.review_id), 
       (SELECT c.price_price FROM prices as c WHERE c.price_product=p.product_id ORDER BY c.price_price ASC LIMIT 1), 
       (SELECT c.price_vendor FROM prices as c WHERE c.price_product=p.product_id ORDER BY c.price_price ASC LIMIT 1) as VID, 
       p.product_url, 
       p.product_clicks, 
       SUM(c.price_clicks), 
       COUNT(c.price_price), 
       c.price_affiliate, 
       (SELECT v.vendor_name FROM vendors as v WHERE v.vendor_id=VID LIMIT 1) 
FROM products as p 
LEFT OUTER JOIN reviews as r ON p.product_id = r.review_product
LEFT OUTER JOIN prices as c ON c.price_product = p.product_id
GROUP BY p.product_id
ORDER BY p.product_clicks DESC
LIMIT 21

Answer 1

您可以将查询重写如下，以减少子查询的影响:

SELECT

main_results.*,

(SELECT c.price_price FROM prices as c WHERE c.price_product=main_results.product_id ORDER BY c.price_price ASC LIMIT 1),
(SELECT c.price_vendor FROM prices as c WHERE c.price_product=main_results.product_id ORDER BY c.price_price ASC LIMIT 1) as VID,
(SELECT v.vendor_name FROM vendors as v WHERE v.vendor_id=VID LIMIT 1)

FROM

(

    SELECT p.product_id, 
           p.product_name, 
           p.product_pic, 
           AVG(r.review_stars), 
           COUNT(DISTINCT r.review_id),                 
           p.product_url, 
           p.product_clicks, 
           SUM(c.price_clicks), 
           COUNT(c.price_price), 
           c.price_affiliate               
    FROM products as p 
    LEFT OUTER JOIN reviews as r ON p.product_id = r.review_product
    LEFT OUTER JOIN prices as c ON c.price_product = p.product_id
    GROUP BY p.product_id
    ORDER BY p.product_clicks DESC
    LIMIT 21

) AS main_results