How to optimize the calculation of MySQL percentile
Editor to share with you how to optimize the MySQL percentile calculation, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
The stored procedure is mainly used, and an index is added to the intermediate calculation result.
The original SQL
Select t5.querytimecommenter t5.tsrecovert2.v from (
Select query_time,total,v, floor (1 + (total-1) * v) rn
From (
Select query_time,count (*) total from t group by query_time
) T3, (select 0.71 vre1 seq union all select 0.81 2 union all select 0.91 3) T4
)
T2 inner join (
Select
Query_time
Case when @ gid=query_time then @ rn:=@rn+1 when @ gid:=query_time then @ rn:=1 end rn
Ts
From (
Select * from t, (select @ gid:='',@rn:=0) vars order by query_time,ts
) T1
) T5 on (t2.query_time=t5.query_time and t2.rn=t5.rn)
Improved to stored procedure
Drop procedure p
Delimiter $$
Create procedure p ()
Begin
Drop table if exists tmp_result
Create temporary table tmp_result (
Query_time date
Rn int
Ts float
Key (query_time,rn)
) engine = memory
Insert ignore into tmp_result
Select
Query_time
Case when @ gid=query_time then @ rn:=@rn+1 when @ gid:=query_time then @ rn:=1 end rn
Ts
From (
Select * from t, (select @ gid:='',@rn:=0) vars order by query_time,ts
) T1
Select t5.querytimecommenter t5.tsrecovert2.v from (
Select query_time,total,v, floor (1 + (total-1) * v) rn
From (
Select query_time,count (*) total from t group by query_time
) T3, (select 0.71 vre1 seq union all select 0.81 2 union all select 0.91 3) T4
)
T2 inner join tmp_result T5 on (t2.query_time=t5.query_time and t2.rn=t5.rn)
End $$
Delimiter
Call p
The use of stored procedures is generally about 11 seconds.
These are all the contents of the article "how to optimize MySQL percentile calculation". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
