Shulou(Shulou.com)06/01 Report--

Follow the previous article

Session overlapping problem learning (2), which is a description of problems and requirements, execution time is 90 seconds

Http://blog.itpub.net/29254281/viewspace-2150229/

Session overlap problem Learning (3)-- Optimization. After one optimization, the execution time is 25 seconds.

Http://blog.itpub.net/29254281/viewspace-2150259/

Session overlap problem learning (4)-- re-optimization, after the second optimization, the execution time is 10 seconds

Http://blog.itpub.net/29254281/viewspace-2150297/

Session overlap problem Learning (5)-- Optimization, after three optimizations, the execution time is 1.6s

Http://blog.itpub.net/29254281/viewspace-2150339/

The algorithm was finally implemented on Friday night.

After working overtime for a week, there is some light at last.

From the origin of this problem, it should have been optimized by 100 times by now.

But on weekends, it's still wrong to think about it.

The merging speed of the little beaver Session merging algorithm (yes, that's what the algorithm will be called) should be very fast. The price is only to scan the record.

What exactly are these 1.6 seconds used for?

Later, after repeated debugging. It is found that there are two areas that can be optimized and improved.

The improved process is as follows:

Drop procedure pbot DELIMITER $$CREATE DEFINER= `root` @ `localhost` PROCEDURE `p` () BEGINdeclare done int default 0 politics v_roomid bigint;declare v_time timestamp (6); declare v_cur_type smallint;declare v_before_roomid bigint default-1 politics v_before_type smallint default-1 v_before_time timestamp (6); declare v_num bigint default 0 politics cur_test CURSOR for select roomid,type,timepoint from tmp_time_point order by roomid,timepoint,type; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 drop table if exists T1 Drop table if exists t2drop table if exists tmp_time_point;drop table if exists tmp_result;drop table if exists tmp_min_range;drop table if exists tmp_s;CREATE temporary TABLE `t1` (`roomid` int (11) NOT NULL DEFAULT '0mm, `userid` bigint (20) NOT NULL DEFAULT' 0mm, `s` timestamp (6), `e` timestamp (6), primary key (roomid,userid,s,e) ENGINE=memory;CREATE temporary TABLE `t2` (`roomid` int (11) NOT NULL DEFAULT '0mm, `s` timestamp (6), `e` timestamp (6) ENGINE=memory CREATE temporary TABLE `tmp_min_ Range` (`roomid` int (11) NOT NULL DEFAULT'0), `s` timestamp (6), `e` timestamp (6), primary key (roomid,s,e), key (roomid,e)) ENGINE=memory;create temporary table tmp_time_point (roomid bigint,timepoint timestamp (6), type smallint,key (roomid,timepoint)) engine=memory;create temporary table tmp_result (roomid bigint,timepoint timestamp (6), c int) engine=memory;create temporary table tmp_s (roomid bigint,userid bigint,s timestamp,e timestamp,i int) engine=memory;SET @ Agg0 SET @ B:=@B+1 AS id,roomid,userid,sFROM insert into tmp_sSELECT x.originidjournal x.useridjournal, b.roomstartAND a.roomstart = b.roomstartAND a.roomend, (SELECT @ B:=@B+1 AS id,roomid,userid,sFROM (SELECT DISTINCT roomid,userid, b.roomstartAND a.roomstart = b.useridAND a.roomstart)

< b.roomend)) AS o) AS y)WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid ;select max(i) into @c from tmp_s;insert ignore into t1(roomid,userid,s,e)selectroomid, userid,if(date(s)!=date(e) and id>

1 efrom tmp_s date (s+interval id-1 date (s+interval id-1 date (e), e dated dated format (s+interval id-1'% Y-%m-%d 23 s+interval id-1 59)) efrom tmp_s T1 date (nums.id b.roomstartAND a.roomstart = b.roomstartAND a.roomend)

< b.roomend)) AS o) AS y)WHERE x.id = y.id AND x.roomid = y.roomid AND x.userid = y.userid ;select max(i) into @c from tmp_s;insert ignore into t1(roomid,userid,s,e)selectroomid, userid,if(date(s)!=date(e) and id>

1Magnum date (s+interval id-1 date (s+interval id-1 date (e), e Magazine datebook format (s+interval id-1'% Y-%m-%d 23 Y-%m-%d 59V 59)) efrom tmp_s T1 STRAIGHT_JOINnums on (nums.id

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.