gpt4 book ai didi

php - 如何处理大型 mysql 查询

转载 作者:太空宇宙 更新时间:2023-11-03 10:57:00 25 4
gpt4 key购买 nike

我有以下问题:

SELECT  p2c.pid AS productNumber,
p.name AS productName
, (
SELECT COUNT(*)
FROM products2customers
WHERE pid = p2c.pid
) AS registered
, (
SELECT COUNT(*)
FROM products2customers
WHERE pid = p2c.pid
AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()
) AS inWarranty
, (
SELECT COUNT(*)
FROM products2customers
WHERE pid = p2c.pid
AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()
) AS outOfWarranty
, (
SELECT DATE_FORMAT( MAX( from_unixtime(purchased) ), '%d.%m.%Y')
FROM products2customers
WHERE pid = p2c.pid
) AS lastPurchased
, (
SELECT DATE_FORMAT( date_add( MAX( from_unixtime(purchased) ), INTERVAL 5 YEAR), '%d.%m.%Y')
FROM products2customers
WHERE pid = p2c.pid
) AS warrantyUntil
FROM (
SELECT DISTINCT
p2c.pid
FROM
products2customers p2c
) AS p2c
JOIN
products p
ON
p.id = p2c.pid
ORDER BY
inWarranty DESC

查询是在一个有 25000 行的数据库表上执行的。此查询的执行时间约为 40 秒。结果将显示在网页上,因此等待 40 秒的结果不是很好。

有没有办法执行这个查询并保存它的输出?因为如果每晚执行此查询就足够了。

执行此操作的最佳方法是什么?我应该创建一个 cronjob 并执行此查询并将结果写入数据库吗?或者有更好的方法吗?

或者我可以优化这个查询,让它更快吗?

最佳答案

我认为所有那些相关的子查询都在折磨你。试试这个:

SELECT  p2c.pid AS productNumber,
p.name AS productName,
COUNT(*) AS registered,
SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()) AS inWarranty,
SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()) AS outOfWarranty,
DATE_FORMAT( MAX( from_unixtime(purchased) ), '%d.%m.%Y') AS lastPurchased,
DATE_FORMAT( date_add( MAX( from_unixtime(purchased) ), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyUntil
FROM products2customers p2c
JOIN products p ON p.id = p2c.pid
GROUP BY p2c.pid
ORDER BY inWarranty DESC

关于php - 如何处理大型 mysql 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19582967/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com