MySQL Forums
Forum List  »  InnoDB

Re: MySQL 5.5.8 - Configuration Settings Suggestions
Posted by: Samir Dash
Date: June 30, 2012 02:20AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.5.8 - Configuration Settings Suggestions
1273
June 30, 2012 02:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.