Re: MySQL 5.5.8 - Configuration Settings Suggestions
Dear Aftab,
Please find the explain details.
EXPLAIN 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, NULL)
AND PRTVRLSTAT.i_statusid IN (1, NULL)
AND NOW() BETWEEN RTVRLSTAT.t_eftfrom
AND RTVRLSTAT.t_eftto
AND NOW() BETWEEN PRTVRLSTAT.t_eftfrom
AND PRTVRLSTAT.t_eftto ;
---------------------------------------------------------------
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 SIMPLE RTVRLSTAT ALL PRIMARY,FK_unv_p4_rule_rtv_static_stts \N \N \N 2002 USING WHERE
1 SIMPLE PRTVRLSTAT EQ_REF PRIMARY,FK_unv_p4_rule_rtv_static_stts PRIMARY 4 unv_lookup.RTVRLSTAT.i_autoid 1 using where
---------------------------------------------------------------
EXPLAIN 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, NULL)
AND PRTVRLSTAT.i_statusid IN (1, NULL)
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)
)
---------------------------------------------------------------
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 SIMPLE RTVRLSTAT ALL PRIMARY,FK_unv_p4_rule_rtv_static_stts \N \N \N 1900
1 SIMPLE PRTVRLSTAT EQ_REF PRIMARY PRIMARY 4 unv_lookup.RTVRLSTAT.i_autoid 1 using where
---------------------------------------------------------------
Original Query - Explain
---------------------------------------------------------------
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 SIMPLE RTVRLSTAT ALL PRIMARY,FK_unv_p4_rule_rtv_static_stts \N \N \N 2002 Using Where
1 SIMPLE PRTVRLSTAT EQ_REF PRIMARY,FK_unv_p4_rule_rtv_static_stts PRIMARY 4 unv_lookup.RTVRLSTAT.i_autoid 1 using where
---------------------------------------------------------------
Regards,
Samir