Re: MySQL 5.5.8 - Configuration Settings Suggestions
Dear Aftab,
Please find the first query.
SELECT c.RTVRLSTATID 'i_autoid',c.i_prsbrlid,c.ENTFLDNAME 'c_entity',c.c_operator,c.c_value,c.c_isregex,IF(ISNULL(c.CMPFLDNAME),'',c.CMPFLDNAME) 'c_cmpentyy,c.c_start,c.c_end,c.c_lglopr FROM (SELECT b.RTVRLSTATID,b.i_prsbrlid,b.i_prjfldid,b.i_operid,OPR.c_operator,b.c_value,b.c_isregex,b.c_cmpentty,b.c_start,b.c_end,b.c_lglopr,PRFLD.i_fieldid 'ENTFLDID',PFLD.c_fldname 'ENTFLDNAME',b.i_fieldid 'CMPFLDID',b.c_fldname 'CMPFLDNAME' from (SELECT a.RTVRLSTATID,a.i_prsbrlid,a.i_prjfldid,a.i_operid,a.c_value,a.c_isregex,a.c_cmpentty,a.c_start,a.c_end,a.c_lglopr,PRJFLD.i_fieldid,PRJFLD.i_autoid,FLD.c_fldname FROM (select RTVRLSTAT.i_autoid 'RTVRLSTATID','RTVRLSTAT.i_prsbrlid,RTVRLSTAT.i_prjfldid,RTVRLSTAT.i_operid,RTVRLSTATS.c_value,RTVRLSTAT.c_isregex,PRTVRLSTAT.c_cmpentty,RTVRLSTAT.c_start,RTVRLSTAT.c_end,RTVRLSTAT.c_lglopr FROM unv_p4_rule_rtv_static RTVRLSTAT LEFT JOIN unv_p4_rule_rtv_static PRTVRLSTAT on RTVRLSTAT.i_autoid=PRTVRLSTAT.i_autoid WHERE RTVRLSTAT.i_statusid in (1) and PRTVRLSTAT.i_statusid in (1) and NOW() BETWEEN RTVRLSTAT.t_eftfrom AND RTVRLSTAT.t_eftto AND NOW() BETWEEN PRTVRLSTAT.t_eftfrom and PRTVRLSTAT.t_eftto OR (ISNULL(RTVRLSTAT.i_statusid) OR ISNULL(PRTVRLSTAT.i_statusid))) a LEFT JOIN unv_prj_field PRJFLD ON PRJFLD.i_autoid=a.c_cmpentty LEFT JOIN unv_field FLD on FLD.i_autoid=PRJFLD.i_fieldid WHERE (PRJFLD.i_statusid in (1) and FLD.i_statusid in (1)) AND NOW() BETWEEN PRJFLD.t_eftfrom AND PRJFLD.t_eftto AND NOW() BETWEEN FLD.t_eftfrom AND FLD.t_eftto OR (ISNULL(PRJFLD.i_statusid) OR ISNULL(FLD.i_statusid))) b LEFT JOIN unv_prj_field PRFLD ON PRFLD.i_autoid=b.i_prjfldid LEFT JOIN unv_field PFLD ON PFLD.i_autoid=PRFLD.i_fieldid LEFT JOIN unv_operator_mst OPR ON b.i_operid=OPR.i_autoid WHERE (PRFLD.i_statusid in (1) AND PFLD.i_statusid in (1) AND OPR.i_statusid in (1)) AND NOW() BETWEEN OPR.t_eftfrom AND OPR.t_eftto AND NOW() BETWEEN PFLD.t_eftfrom and PFLD.t_eftto AND NOW() BETWEEN PRFLD.t_eftfrom AND PRFLD.t_eftto OR (ISNULL(PRFLD.i_statusid) OR ISNULL(PFLD.i_statusid) OR ISNULL(OPR.i_statusid))) c, unv_prj_rtv_subrl_mst SBRLMST,unv_prj_field_runtime FLDRUNTME,unv_prj_field_runtime_rule_mst FLDRUNMST WHERE c.i_prsbrlid=SBRLMST.i_autoid AND FLDRUNTME.i_autoid=FLDRUNMST.i_fldrtvid AND SBRLMST.i_statusid in (1) AND FLDRUNMST.i_statusid in (1) AND FLDRUNTME.i_p4id in (271,285,631,632) and FLDRUNTME.i_statudid in (1) AND NOW() BETWEEN SBRLMST.t_eftfrom AND SBRLMST.t_eftto AND NOW() BETWEEN FLDRUNTIME.t_eftfrom AND FLDRUNTME.t_eftto AND NOW() BETWEEN FLDRUNMST.t_eftfrom AND FLDRUNMST.t_eftto OR (ISNULL(SBRLMST.i_statusid) OR ISNULL(FLDRUNTME.i_statusid) OR ISNULL(FLDRUNMST.i_statusid) GROUP BY c.RTVRLSTATID