Hello, I have this query in my system which takes around 2.5 seconds
to run. I have diagnosed that the problem is actually a hashjoin on
perm and s_ast_role tables. Is there a way I can avoid that join? I
just want to change the
query and no environment change.
SELECT
ai.aid,
SUM(ai.score) as search_score
FROM
sq_sch_idx ai,
(
SELECT
a.aid
FROM
t a
INNER JOIN slink l ON l.minorid = a.aid
INNER JOIN slink_tree t ON t.linkid = l.linkid
WHERE
(t.treeid LIKE '0005%')
AND a.status >= '16'
AND a.type_code IN
(
SELECT type_code FROM t_typ_inhd WHERE inhd_type_code IN
('page') OR type_code IN ('file', 'page_rss_feed')
)
AND a.aid IN
(
SELECT
p.aid
FROM
perm p LEFT JOIN s_ast_role r ON (p.userid = r.roleid)
WHERE
(
p.userid IN ('7') OR r.userid IN ('7')
) AND
(
(
p.permission = '1'
AND
(
(
p.userid <> '7' OR
(r.userid IS NULL OR r.userid <> '7')
)
OR
(p.userid = '7' AND granted = '1')
OR
(r.userid = '7' AND granted = '1')
)
)
OR
(p.permission > '1' AND p.granted = '1')
)
AND a.aid=p.aid
GROUP BY
p.aid
HAVING
MIN(p.granted) <> '0'
)
) asset_check
WHERE
(ai.aid=asset_check.aid)
AND (ai.value LIKE '%download%')
GROUP BY
ai.aid
;
Thanks
