Learning Session overlapping problems (4)-- re-optimization

Following the previous article:

Requirements description and first version solution (execution time 90 seconds)

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

Optimize and modify the version of bug (execution time 25 seconds)

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

I think the previous article has reached the best in the way of collective thinking.

If you give up the complete set processing thinking, you can actually be more optimized.

Several questions mentioned in the previous article.

1. Too many table structures have been introduced.

two。 It takes time to write the table itself.

3. The previous batch processing, the particle size is still fine. It should be the fastest to get all the batches out.

4. The previous part of calculating the minimum interval range, because of the application of collective thinking, difficult to understand the performance is still poor.

The previous part of calculating the minimum interval range is as follows

Select roomid,as DATETIME) starttime,as DATETIME) endtime from (select @ d as starttime,@d:=d,v3.roomid,v3.d endtime from (select distinct roomid,when nums.id=1 then v1swhen nums.id=2 then v1ewhen nums.id=3 then v2swhen nums.id=4 then v2eend d from (select v1.roomid, v1.s v1sMagi v1.e v1e v2.s v2s) V2.e v2efrom T1 v1inner join T1 v2 on ((v1.s between v2.s and v2.e or v1.e between v2.s and v2.e) and v1.roomid=v2.roomid) where v2.roomid in (select distinct roomid from T1 where date (s) = pTime) and v2.s > = pTime and v2.s = b.roomstartAND a.roomend

S.sAND s.roomid = s2.roomidAND s.userid = s2.userid) AS eFROM (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) eWHERE s.roomid = e.roomidAND s.userid = e.userid) T1, numswhere nums.id1) v7 group by roomid,date (s); END

After all are condensed into a process, there is no need to create additional regular tables, just create temporary tables in the process. Achieve high cohesion and low coupling.

Call p

The result returned by the process is the final result.

All three tests took less than 10.3 seconds.