MySQL Forums
Forum List  »  InnoDB

Re: MySQL 5.5.8 - Configuration Settings Suggestions
Posted by: Aftab Khan
Date: June 30, 2012 01:27AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.5.8 - Configuration Settings Suggestions
1334
June 30, 2012 01:27AM


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.