php - 如何让 Mysql 查询(多连接)更快更高效

Question

我在执行一个非常慢的 MySql 查询时遇到了一个大问题......太慢了......无法使用!

阅读 1000 种产品及其价格需要 20 多秒!!!

$dati = mysqli_query($mysqli_connect, "
SELECT      *  
FROM        $tb_products
LEFT JOIN   $tb_categories ON $tb_products.product_category = $tb_categories.category_id_master
LEFT JOIN   $tb_subcategories ON $tb_products.product_subcategory = $tb_subcategories.subcategory_id_master
LEFT JOIN   $tb_logos ON $tb_products.product_logo = $tb_logos.logo_id_master

LEFT JOIN   $tb_prices ON ( 
            $tb_products.product_brand = $tb_prices.price_brand
            AND $tb_products.product_code = $tb_prices.price_code
            AND $tb_prices.price_validity = (
                SELECT  MAX($tb_prices.price_validity) 
                FROM    $tb_prices 
                WHERE   $tb_prices.price_validity<=DATE_ADD(CURDATE(), INTERVAL +0 DAY)
                        AND $tb_products.product_code = $tb_prices.price_code
            )
        )

WHERE       $tb_products.product_language='$activeLanguage' AND $tb_products.product_category!=0
GROUP BY    $tb_products.product_code
ORDER BY    $tb_products.product_brand, $tb_categories.category_rank, $tb_subcategories.subcategory_rank, $tb_products.product_subcategory, $tb_products.product_rank
");

编辑:
正如 Mr.Alvaro 所建议的那样，我已经更改了 SELECT * 具有更高效的 SELECT [值列表] 并且执行时间从 20 秒减少到 14 秒。还是太慢了...
结束编辑

每个产品可以有不同的价格，所以我使用 (select max...) 来获取最近(但不是 future )的价格。也许这个功能会减慢一切？您认为有更好的解决方案吗？

考虑一下，没有加入价格的同一个查询只需要 0.2 秒。所以我确信问题出在那部分代码中。

$dati = mysqli_query($mysqli_connect, "
SELECT      *  
FROM        $tb_products
LEFT JOIN   $tb_categories ON $tb_products.product_category = $tb_categories.category_id_master
LEFT JOIN   $tb_subcategories ON $tb_products.product_subcategory = $tb_subcategories.subcategory_id_master
LEFT JOIN   $tb_logos ON $tb_products.product_logo = $tb_logos.logo_id_master

WHERE       $tb_products.product_language='$activeLanguage' AND $tb_products.product_category!=0
GROUP BY    $tb_products.product_code
ORDER BY    $tb_products.product_brand, $tb_categories.category_rank, $tb_subcategories.subcategory_rank, $tb_products.product_subcategory, $tb_products.product_rank
");

我还考虑到它可能取决于服务器的能力这一事实，但我倾向于将其排除在外，因为第二个查询(没有价格)作为速度是完全可以接受的。

价格表如下

+----------------+-------------+
| price_id       | int(3)      |
| price_brand    | varchar(5)  |
| price_code     | varchar(50) |
| price_value    | float(10,2) |
| price_validity | date        |
| price_language | varchar(2)  |
+----------------+-------------+

Answer 1

可能是因为您正在使用 SELECT *，这被称为不良做法。在堆栈溢出中检查此问题。

Is there a difference between Select * and Select [list each col]

在那里，Mitch Wheat 写道:

You should specify an explicit column list. SELECT * will bring back more columns than you need creating more IO and network traffic, but more importantly it might require extra lookups even though a non-clustered covering index exists (On SQL Server). Blockquote