一个性能难题,如何优化如下sql?
select trusteeship_company,server_ip,ri.re_id,sum(visit_count) visit_count, max(max_time) max_time from( select ua.re_id,ua.protocol_type,count(vr.web_visit_id) visit_count, max(vr.visit_time) max_time from url_addr ua,web_visit_record vr where vr.web_id=ua.web_id and ua.protocol_type in(10,20) group by ua.re_id union all select ua.re_id,ua.protocol_type,count(vr.webmail_visit_id) visit_count,max(vr.visit_time) max_time from url_addr ua,webmail_visit_record vr where vr.web_id=ua.web_id and ua.protocol_type=15 group by ua.re_id union all select ua.re_id,ua.protocol_type,count(vr.video_visit_id) visit_count,max(vr.visit_time) max_time from url_addr ua,video_visit_info vr where vr.web_id=ua.web_id and ua.protocol_type in(18,19) group by ua.re_id union all select vr.re_id,11 protocol_type,count(vr.ftp_visit_id) visit_count,max(vr.visit_time) max_time from ftp_visit_record vr where 1=1 group by vr.re_id union all select vr.re_id,90 protocol_type,count(vr.mail_visit_id) visit_count,max(vr.visit_time) max_time from mail_visit_record vr where 1=1 group by vr.re_id union all select vr.re_id,12 protocol_type,count(vr.telnet_visit_id) visit_count,max(vr.visit_time) max_time from telnet_visit_record vr where 1=1 group by vr.re_id ) result,main_info mi,idc_ip_source iis,re_idcpc ri where ri.re_id=result.re_id and ri.main_id=mi.main_id and ri.source_id=iis.source_id group by re_id order by visit_count desc