如何处理大型mysql查询
我有以下查询:SELECTp2c.pid AS productNumber,
p.name AS productName
, (
SELECT COUNT(*)
FROM products2customers
WHEREpid = p2c.pid
) AS registered
, (
SELECT COUNT(*)
FROM products2customers
WHEREpid = p2c.pid
AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()
) AS inWarranty
, (
SELECT COUNT(*)
FROM products2customers
WHEREpid = p2c.pid
AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) 查询在具有25.000行的数据库表上执行。该查询的执行时间约为40秒。结果将显示在网页上,因此等待40秒以获取结果并不是很好。
有没有一种方法可以执行此查询并保存其输出?因为如果每天晚上执行此查询就足够了。
最好的方法是什么?我应该创建一个cronjob并执行此查询并将结果写入数据库吗?或者,还有更好的方法?
或者我可以优化此查询以使其更快?
解决方案:
我认为所有这些相关的子查询都在杀死。试试这个:
SELECTp2c.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
页:
[1]