Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.