mysql - 合并两个查询时有什么方法可以优化这个 MySQL 查询？

Question

我试图通过 product_id, stock, new_cost 获得结果

SELECT inventory_logs.product_id, ROUND(SUM(inventory_logs.qty + 0), 4) AS stock
FROM products
JOIN inventory_logs on products.id = inventory_logs.product_id
WHERE inventory_logs.`type` = 'as'
AND inventory_logs.`created_at` between '1970-01-01' and '2021-02-22 23:59:59'
AND inventory_logs.`branch_id` = 1
AND inventory_logs.`deleted_at` IS NULL AND products.deleted_at IS NULL
GROUP BY inventory_logs.product_id HAVING stock > 0

以上是获取库存的查询，查询时间约为0.031秒

SELECT inventory_logs.product_id, inventory_logs.new_cost
FROM inventory_logs JOIN(
    SELECT product_id, MAX(created_at) AS created_at
    FROM inventory_logs
    WHERE created_at <= '2021-02-22 23:59:59' AND deleted_at IS NULL
    AND `type` = 'as'
    AND inventory_logs.`branch_id` = 1
    GROUP BY product_id
) i2 ON (inventory_logs.product_id = i2.product_id AND inventory_logs.created_at = i2.created_at)
WHERE `type` = 'as'
AND inventory_logs.`branch_id` = 1

以上是获取new_cost的查询，查询的持续时间也是0.031秒左右

但是当我尝试将上述两个查询合并为一个时，查询的持续时间是 26.016 sec 查询是

SELECT inventory_logs.product_id, ROUND(SUM(inventory_logs.qty = 0), 4) AS stock, sub.new_cost
FROM products
JOIN inventory_logs ON products.id = inventory_logs.product_id
JOIN (
    SELECT inventory_logs.product_id, inventory_logs.new_cost
    FROM inventory_logs JOIN (
        SELECT product_id, MAX(created_at) AS created_at
        FROM inventory_logs
        WHERE created_at <= '2021-02-22 23:59:59' AND deleted_at IS NULL
        AND `type` = 'as'
        AND inventory_logs.`branch_id` = 1
        GROUP BY product_id
     ) i2 ON (inventory_logs.product_id = i2.product_id AND inventory_logs.vreated_at 
     = i2.created_at)
    WHERE `type` = 'as'
    AND inventory_logs.`branch_id` = 1
) sub ON products.id = sub.product_id
WHERE inventory_logs.`type` = 'as'
AND inventory_logs.`created_at` BETWEEN '1970-01-01' AND '2021-02-22 23:59:59'
AND inventory_logs.`branch_id` = 1
AND inventory_logs.`deleted_at` IS NULL
AND products.deleted_at IS NULL
GROUP BY inventory_logs.product_id HAVING stock > 0

有什么方法可以优化上述查询以减少时间？

标签： mysql