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

It's 10:00 on Friday night.

This week continuously optimizes the Session merge and split problem. The performance is more than double that of the previous day every day.

Finally today, the original little beaver Session merging algorithm has reached the optimal level.

An exciting 1.5 to 2 second level.

It's getting late, and the train of thought is a little unclear. Post the code first. Explain this wonderful algorithm in detail next week.

DELIMITER $$CREATE DEFINER= `root` @ `localhost`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 politics 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 localhost` drop table if exists T2 Drop table if exists tmp_time_point;drop table if exists tmp_result;drop table if exists tmp_min_range;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` (`roomid`, `s`, `e`, `userid`) ENGINE=memory;CREATE temporary TABLE `t2` (`roomid` int (11) NOT NULL DEFAULT '0mm, `s` timestamp (6), `e` timestamp (6), primary KEY `roomid` (`roomid`, `s`, `e`) ENGINE=memory CREATE temporary TABLE `tmp_min_ Range` (`roomid` int (11) NOT NULL DEFAULT'0), `s` timestamp (6), `e` timestamp (6), primary KEY `rangid` (`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;SET @ Abilid` Insert into T1 (roomid,userid,s,e) select distinctroomid, userid,if (date (s)! = date (e) and id > 1 efrom date (s+interval id-1 date (s+interval id-1 date (e), eMagicaldatedatedate format (s+interval id-1'% Y-%m-%d select distinctroomid 2359 select distinctroomid 59)) efrom (SELECT @ B:=@B+1 AS id,roomid,userid,sFROM (SELECT DISTINCT roomid,userid) Roomstart AS sFROM u_room_log aWHERE NOT EXISTS (SELECT * FROM u_room_log bWHERE a.roomid = b.roomidAND a.userid = b.useridAND a.roomstart > 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) T1, numswhere nums.id2) a group by roomid,date (s) END

Compared with the previous algorithm, the result is consistent. Basically, it is about 1.6 seconds.

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.