|
我有以下查询:( B$ d; O* w* F$ K* x
SELECT p2c.pid AS productNumber,
% K Q- Z# S. ] p.name AS productName
7 u; {, _6 z' q# U g& x , (
, o0 H0 k9 L* U SELECT COUNT(*)
! |7 @' o/ h. q }: @& }; |- N FROM products2customers5 Y4 o( i5 |% ?( S2 W2 z# U
WHERE pid = p2c.pid
5 e8 }9 s0 g% @ d) _ ) AS registered/ w* d& O! A0 M# m* ~
, (8 O; |: g' H. k( l Q( ~6 c
SELECT COUNT(*)) ^% h% I* f! Q6 _7 |6 c
FROM products2customers |; e l6 D; q3 e0 {. F" O7 ]
WHERE pid = p2c.pid
! N! k# S. \- w# G AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()
6 _6 a6 A4 G, r7 R ) AS inWarranty
3 w" g; d3 ~0 C9 W! R0 U , (7 ^% i0 Q+ K9 x ^" @8 ^ f" \
SELECT COUNT(*)
' @$ v. R" w8 a' } FROM products2customers
- {! ?) j7 {0 a v9 Z WHERE pid = p2c.pid
( U" j- l5 N+ L/ ? AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) 查询在具有25.000行的数据库表上执行。该查询的执行时间约为40秒。结果将显示在网页上,因此等待40秒以获取结果并不是很好。
5 v! i* `. }7 y/ `4 I/ H有没有一种方法可以执行此查询并保存其输出?因为如果每天晚上执行此查询就足够了。
]9 ?% S9 ]3 h+ G4 }' }最好的方法是什么?我应该创建一个cronjob并执行此查询并将结果写入数据库吗?或者,还有更好的方法?: f2 ~7 j! f/ Y+ A
或者我可以优化此查询以使其更快?
; k+ \( |/ Y$ e
, h8 G; Y" q" V6 I- m& d解决方案:
* H9 n G/ q* U+ C! S F- y# U
9 U. q% X l& e* |% g, B# h, E* m6 w4 w& k7 f
' N' Z) \0 D9 |" L+ V! w8 F
我认为所有这些相关的子查询都在杀死。试试这个:
2 `8 X3 i7 k6 d' v R/ S/ RSELECT p2c.pid AS productNumber,4 ?) W: q6 H' g3 M; }
p.name AS productName,
( x6 H5 i* k2 z( B9 j7 b COUNT(*) AS registered,8 Y9 {6 X4 E* t7 \
SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()) AS inWarranty,
8 E8 M0 W6 X& N6 P! T" _/ ^ SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()) AS outOfWarranty,
- o/ G! t1 c& c( q. @6 I DATE_FORMAT( MAX( from_unixtime(purchased) ), '%d.%m.%Y') AS lastPurchased,
9 O5 M1 H9 Q2 n r DATE_FORMAT( date_add( MAX( from_unixtime(purchased) ), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyUntil
+ g" }7 v+ ^; M' U/ lFROM products2customers p2c
6 |, p& V G1 q6 H+ rJOIN products p ON p.id = p2c.pid
) s! L" A1 {5 }' O# {4 LGROUP BY p2c.pid# x/ _6 b7 c( h+ [8 T% h( z
ORDER BY inWarranty DESC |
|