Re: MySQL 5.5.8 - Configuration Settings Suggestions
All of your sub-queries doing full table scans, derived tables don't have indexes, so you may create TEMPORARY table (with required indexes) for them and join temp tables with the main query
-- <derived2>
-- <derived3>
-- <derived4>
and the sub-queries needs optimization:#
>or (isnull(RTVRLSTAT.i_statusid) or isnull(PRTVRLSTAT.i_statusid # sub-query(a)
> or (isnull(PRJFLD.i_statusid)or isnull(FLD.i_statusid) # sub-query (b)
I don't think these 'ISNULL' checks are needed on both tables?
Can you provide output of the following sql:
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 ;
# and the following SQL
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)
)
Also, do run above sqls without EXPLAIN as well, are the output identical?
Edited 2 time(s). Last edit at 06/30/2012 01:43AM by Aftab Khan.