|
我有以下查询: S0 F9 A+ w+ _3 V' v; Z* U Q
SELECT p2c.pid AS productNumber,' |# x9 _ ~; V9 }6 @8 Z
p.name AS productName
* x7 R& E* P+ f: A6 t , (
3 x! F5 c% h/ b" [& o' Y- H SELECT COUNT(*). v- k# C* X& j9 [+ C* k5 k" j
FROM products2customers
8 Y# `7 c4 C( Q) b" e8 M; g WHERE pid = p2c.pid
8 E1 S Z6 I$ U1 e' } ) AS registered
2 {: R6 y$ G; ]5 c , (
3 \5 w; k$ e: }6 h SELECT COUNT(*)
# M9 S6 `9 G" r% g" Q/ y FROM products2customers
% V/ d. _% @0 Y l# e8 t1 B WHERE pid = p2c.pid1 B; X R% c" p- _
AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()$ W3 a Q' t* a9 A" x
) AS inWarranty. Y& D1 i5 H8 r. w8 X" g, q
, (
: X5 E3 l: t Q SELECT COUNT(*)
& z" M2 A* D0 q6 l9 E- u FROM products2customers' i# m9 l3 ]$ z0 X6 _
WHERE pid = p2c.pid8 y0 _+ n( v) R6 }
AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) 查询在具有25.000行的数据库表上执行。该查询的执行时间约为40秒。结果将显示在网页上,因此等待40秒以获取结果并不是很好。
% J! k Y$ T$ V" V }" `% |5 z有没有一种方法可以执行此查询并保存其输出?因为如果每天晚上执行此查询就足够了。
& `6 ~ U) a* ~' L最好的方法是什么?我应该创建一个cronjob并执行此查询并将结果写入数据库吗?或者,还有更好的方法?
- B/ O0 D1 x) Z- x( u/ \2 |. ?3 H或者我可以优化此查询以使其更快?
# t/ [9 }( h( C0 S5 l# @+ C$ N $ ?3 {7 s% h; o, W& s$ E
解决方案:3 ~7 d1 H0 U8 d) Z& S
& S0 m$ M# ]9 J' i0 y
; D6 n6 Q- h0 d& i
; {. h6 k g' S& o3 j, X4 m6 y- H 我认为所有这些相关的子查询都在杀死。试试这个:6 N4 y" F5 |4 P3 u" {
SELECT p2c.pid AS productNumber,
$ a* L* G/ X H/ Q1 f5 q- _ p.name AS productName,6 ]& ^# j2 L; y1 s: L& o! R- N
COUNT(*) AS registered,
; }7 Q& o v7 ?7 [' j SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()) AS inWarranty,; e8 t6 |; ^& N. U. T' I6 P! M. j
SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()) AS outOfWarranty,
) @% J8 n# j: R2 u DATE_FORMAT( MAX( from_unixtime(purchased) ), '%d.%m.%Y') AS lastPurchased,
. B6 z# V, v, w5 j p1 w DATE_FORMAT( date_add( MAX( from_unixtime(purchased) ), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyUntil# e6 q7 Z& R5 I& ]+ f* P9 X
FROM products2customers p2c; V2 _) ] W+ d; G# O/ z5 ]. }
JOIN products p ON p.id = p2c.pid& w2 D: b' l) W! Z
GROUP BY p2c.pid
4 b# U) c1 O+ F. E4 `/ {7 Q( FORDER BY inWarranty DESC |
|