Re: MySQL 5.5.8 - Configuration Settings Suggestions
Dear Aftab,
Please find the details.
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY FLDRUNTME ref PRIMARY,FK_unv_prj_field_runtime _stts,FK_unv_prj_field_runtime_p4 FK_unv_prj_field_runtime_p4 4 const 381 Using where; Using temporary; Using filesort
1 PRIMARY FLDRUNMST ref FK_unv_prj_field_runtime_rule_mst_stts,FK_unv_prj_field_runtime_rule_mst FK_unv_prj_field_runtime_rule_mst 4 unv_lookup.FLDRUNTME.i_autoid 2 Using where
1 PRIMARY <derived2> ALL \N \N \N \N 1967 Using join buffer
1 PRIMARY SBRLMST eq_ref PRIMARY,FK_unv_prj_rtv_subrl_mst_stts PRIMARY 4 c.i_prsbrlid 1 Using where
2 DERIVED <derived3> ALL \N \N \N \N 1967
2 DERIVED PRFLD eq_ref PRIMARY PRIMARY 4 b.i_prjfldid 1
2 DERIVED PFLD eq_ref PRIMARY PRIMARY 4 unv_lookup.PRFLD.i_fieldid 1
2 DERIVED OPR eq_ref PRIMARY PRIMARY 4 b.i_operid 1 Using where
3 DERIVED <derived4> ALL \N \N \N \N 1967
3 DERIVED PRJFLD eq_ref PRIMARY PRIMARY 4 a.c_cmpentty 1
3 DERIVED FLD eq_ref PRIMARY PRIMARY 4 unv_lookup.PRJFLD.i_fieldid 1 Using where
4 DERIVED RTVRLSTAT ALL FK_unv_p4_rule_rtv_static_stts \N \N \N 2111
4 DERIVED PRTVRLSTAT eq_ref PRIMARY PRIMARY 4 unv_lookup.RTVRLSTAT.i_autoid 1 Using where
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_cmpentty',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,RTVRLSTAT.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 PRFLD.t_eftfrom and PRFLD.t_eftto and now() between PFLD.t_eftfrom and PFLD.t_eftto
and now() between OPR.t_eftfrom and OPR.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_statusid in (1) and now() between SBRLMST.t_eftfrom and SBRLMST.t_eftto and
now() between FLDRUNTME.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
SHOW Create Tables:
CREATE TABLE `unv_p4_rule_rtv_static` (
`i_autoid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment number',
`i_prsbrlid` int(5) NOT NULL COMMENT 'this is reference to unv_prj_rtv_subrl_mst.i_autoid',
`i_prjfldid` int(5) NOT NULL COMMENT 'reference of unv_prj_field of I_autoid',
`i_operid` int(5) NOT NULL COMMENT 'reference of unv_operator_mst of I_autoid',
`c_value` varchar(240) NOT NULL DEFAULT '' COMMENT 'It will have the value of entity',
`c_isregex` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'It will have the regex type value',
`c_cmpentty` varchar(10) NOT NULL COMMENT 'It will have the name of entity like field name',
`c_cmpoptr` varchar(10) NOT NULL COMMENT 'It will have the operator type like >',
`c_cmpvalue` varchar(20) NOT NULL COMMENT 'It will have the value of entity',
`c_cmpisreg` varchar(100) NOT NULL COMMENT 'It will have the value of regex',
`c_start` varchar(3) NOT NULL COMMENT 'It will have the start indicator',
`c_end` varchar(3) NOT NULL COMMENT 'it will have end indicator',
`c_lglopr` varchar(10) NOT NULL COMMENT 'it will have the operator like and ,or',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'It will have the code of associate performing the activity',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'It will have the date from which it will be effective',
`t_eftto` datetime NOT NULL COMMENT 'It will have the effective from date and time',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
KEY `FK_unv_p4_rule_rtv_static_stts` (`i_statusid`),
KEY `FK_unv_p4_rule_rtv_static` (`i_prsbrlid`),
KEY `FK_unv_p4_rule_rtv_static_3` (`i_prjfldid`),
KEY `FK_unv_p4_rule_rtv_static_4` (`i_operid`),
CONSTRAINT `FK_unv_p4_rule_rtv_static` FOREIGN KEY (`i_prsbrlid`) REFERENCES `unv_prj_rtv_subrl_mst` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_p4_rule_rtv_static_3` FOREIGN KEY (`i_prjfldid`) REFERENCES `unv_prj_field` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_p4_rule_rtv_static_4` FOREIGN KEY (`i_operid`) REFERENCES `unv_operator_mst` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_p4_rule_rtv_static_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=2042 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
CREATE TABLE `unv_prj_field` (
`i_autoid` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment number',
`i_p4id` int(10) NOT NULL COMMENT 'It will have the reference of i_autoid of unv_p4',
`i_fieldid` int(10) NOT NULL COMMENT 'It will have the reference of i_autoid of unv_field',
`c_caption` varchar(20) NOT NULL DEFAULT '' COMMENT 'It will have the caption of field',
`c_length` varchar(5) NOT NULL COMMENT 'It will have the length of field',
`i_formatid` int(5) NOT NULL COMMENT ') It will have the reference of i_autoid of unv_prj_frmt',
`c_iscapreq` enum('N','Y') NOT NULL DEFAULT 'N' COMMENT 'Caption required',
`c_control` varchar(20) NOT NULL COMMENT 'It will be used to have the control type of the field ',
`c_tooltip` varchar(200) NOT NULL COMMENT ' it will have the default tool tips ',
`c_valdchar` text NOT NULL COMMENT 'It will store Regex for Validating data of this field',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'It will have the code of associate performing the activity',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'It will have the date from which it will be effective',
`t_eftto` datetime NOT NULL COMMENT 'It will have the effective from date and time',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
UNIQUE KEY `i_projid` (`i_p4id`,`i_fieldid`),
UNIQUE KEY `i_p4id` (`i_p4id`,`i_fieldid`),
KEY `FK_unv_prj_field_stts` (`i_statusid`),
KEY `FK_unv_prj_field` (`i_fieldid`),
KEY `FK_unv_prj_field_5` (`i_formatid`),
CONSTRAINT `FK_unv_prj_field` FOREIGN KEY (`i_fieldid`) REFERENCES `unv_field` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_prj_field_p4` FOREIGN KEY (`i_p4id`) REFERENCES `unv_p4` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_prj_field_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`),
CONSTRAINT `unv_prj_field_ibfk_1` FOREIGN KEY (`i_formatid`) REFERENCES `unv_format` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3843 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 2299904 kB'
CREATE TABLE `unv_field` (
`i_autoid` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment number',
`c_fldname` varchar(10) NOT NULL COMMENT 'It will have the name of field',
`c_datatype` varchar(10) NOT NULL COMMENT 'it will have the data type of field',
`c_desc` varchar(100) NOT NULL COMMENT 'Description of field name',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'It will have the code of associate performing the activity',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'It will have the date from which it will be effective',
`t_eftto` datetime NOT NULL COMMENT 'It will have the effective from date and time',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
UNIQUE KEY `c_fldname` (`c_fldname`),
KEY `FK_unv_field_stts` (`i_statusid`),
CONSTRAINT `FK_unv_field_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=1180 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 2434048 kB'
CREATE TABLE `unv_operator_mst` (
`i_autoid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment number',
`c_operator` varchar(10) NOT NULL COMMENT 'It is operatyor type',
`c_desc` varchar(100) NOT NULL COMMENT 'description of operator type',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'It will have the code of associate performing the activity',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'It will have the date from which it will be effective',
`t_eftto` datetime NOT NULL COMMENT 'It will have the effective from date and time',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
UNIQUE KEY `i_projid` (`c_operator`,`c_desc`),
KEY `FK_unv_p41` (`c_desc`),
KEY `FK_unv_p4_stts` (`i_statusid`),
CONSTRAINT `FK_unv_operator_mst_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
CREATE TABLE `unv_prj_rtv_subrl_mst` (
`i_autoid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'it is autoid and is autoincrement and unique',
`i_rntimeid` int(5) NOT NULL COMMENT 'this is reference to unv_prj_field_runtime_rule_dtl.i_autoid',
`c_srtype` varchar(10) NOT NULL COMMENT 'this field stores rule type',
`c_subrule` varchar(100) NOT NULL COMMENT 'this stores subrulename',
`i_priority` int(5) NOT NULL COMMENT 'this stores priority for subrules',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'Will contains information about user',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'Will Contain manually keyed or auto generated date & time. This column will provide input about availability of information source in live enviornment.',
`t_eftto` datetime NOT NULL COMMENT 'Will contains date & time of information source creation',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
KEY `FK_unv_prj_rtv_subrl_mst` (`i_rntimeid`),
KEY `FK_unv_prj_rtv_subrl_mst_stts` (`i_statusid`),
CONSTRAINT `FK_unv_prj_rtv_subrl_mst` FOREIGN KEY (`i_rntimeid`) REFERENCES `unv_prj_field_runtime_rule_mst` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_prj_rtv_subrl_mst_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=1785 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
CREATE TABLE `unv_prj_field_runtime` (
`i_autoid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment number',
`i_p4id` int(5) NOT NULL COMMENT 'It will have reference with i_autoid of unv_p4id',
`i_fmtscid` int(5) NOT NULL COMMENT 'It will have reference with i_autoid of unv_prj_frmt_screen',
`c_event` varchar(10) NOT NULL COMMENT 'It will have the event name like got focus lost focus',
`c_statrtyp` enum('Y','N') NOT NULL COMMENT 'is it static rule type or not it will have value "Y" or "N"',
`c_lkprtyp` enum('Y','N') NOT NULL COMMENT 'is it lookup rule type or not it will have value "Y" or "N"',
`c_isrule` enum('Y','N') NOT NULL COMMENT 'is it rule or not it will have value "Y" or "N"',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'It will have the code of associate performing the activity',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'It will have the date from which it will be effective',
`t_eftto` datetime NOT NULL COMMENT 'It will have the effective from date and time',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
KEY `FK_unv_prj_field_runtime _stts` (`i_statusid`),
KEY `FK_unv_prj_field_runtime_p4` (`i_p4id`),
KEY `FK_unv_prj_field_runtime1` (`i_fmtscid`),
CONSTRAINT `FK_unv_prj_field_runtime1` FOREIGN KEY (`i_fmtscid`) REFERENCES `unv_prj_frmt_screen` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_prj_field_runtime_p4` FOREIGN KEY (`i_p4id`) REFERENCES `unv_p4` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_prj_field_runtime_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=2032 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
CREATE TABLE `unv_prj_field_runtime_rule_mst` (
`i_autoid` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment number',
`c_rulename` varchar(100) NOT NULL COMMENT 'It will have reference with i_autoid of unv_p4id',
`c_event` varchar(10) NOT NULL COMMENT 'It will have the event name like got focus lost focus',
`c_statrtyp` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'is it static rule type or not it will have value "Y" or "N"',
`c_lkprtyp` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'is it lookup rule type or not it will have value "Y" or "N"',
`i_fldrtvid` int(5) NOT NULL COMMENT 'It will have reference with i_autoid of unv_prj_field_runtime ',
`t_timestmp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'It will contains date & time information when row is inserted',
`c_logby` char(3) NOT NULL COMMENT 'It will have the code of associate performing the activity',
`i_statusid` int(2) NOT NULL DEFAULT '1' COMMENT 'It will have the reference of i_autoid of unv_status',
`t_eftfrom` datetime NOT NULL COMMENT 'It will have the date from which it will be effective',
`t_eftto` datetime NOT NULL COMMENT 'It will have the effective from date and time',
`t_syncdttm` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'This column used to distinguish last sync date and data which requires sync or data which requires insert',
PRIMARY KEY (`i_autoid`),
KEY `FK_unv_prj_field_runtime_rule_mst_stts` (`i_statusid`),
KEY `FK_unv_prj_field_runtime_rule_mst_p4` (`c_rulename`),
KEY `FK_unv_prj_field_runtime_rule_mst` (`i_fldrtvid`),
CONSTRAINT `FK_unv_prj_field_runtime_rule_mst` FOREIGN KEY (`i_fldrtvid`) REFERENCES `unv_prj_field_runtime` (`i_autoid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_unv_prj_field_runtime_rule_mst_stts` FOREIGN KEY (`i_statusid`) REFERENCES `unv_status` (`i_autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=1663 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
CREATE TABLE `unv_status` (
`i_autoid` int(2) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`c_statdesc` varchar(10) NOT NULL COMMENT 'Will store explaination of row status defined',
`c_deleted` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'will contain row status',
`c_user` char(3) NOT NULL COMMENT 'Will contains information about user',
`t_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Will contains date & time of information source creation',
PRIMARY KEY (`i_autoid`),
KEY `idx_del` (`c_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
Thanks
Samira Kumar Dash