mysql query performance issue

[gilbertsavier]

Hi All,

I am using testlink tool with mysql ( teamst.org/phpBB2/viewtopic.php?t=1938 ).

I have found that this query takes 54 secs to fetch data..which is not acceptable..
I am not conversant with mysql tuning...

SELECT
NHB.parent_id AS testsuite_id, NHA.parent_id AS tc_id,
NHB.node_order AS z, T.tcversion_id AS tcversion_id,
T.id AS feature_id, TCV.active,
E.id AS exec_id, E.tcversion_id AS executed,
E.testplan_id AS exec_on_tplan, UA.user_id,UA.type,
UA.status, UA.assigner_id,
COALESCE(E.status,'n') AS exec_status
FROM
nodes_hierarchy NHA JOIN nodes_hierarchy NHB ON NHA.parent_id = NHB.id
JOIN testplan_tcversions T ON NHA.id = T.tcversion_id
JOIN tcversions TCV ON NHA.id = TCV.id
JOIN executions E ON (NHA.id = E.tcversion_id AND E.testplan_id=T.testplan_id AND E.build_id=21 )
LEFT OUTER JOIN user_assignments UA ON UA.feature_id = T.id
WHERE T.testplan_id=30397
AND (UA.type=1 OR UA.type IS NULL)
AND E.status='p'
AND E.id IN ( SELECT MAX(id) FROM executions WHERE testplan_id=30397 GROUP BY tcversion_id,testplan_id )
ORDER BY testsuite_id,NHB.node_order,tc_id,E.id ASC;



Any help to bring down the sql execution time would be highly appreciated.
_________________
Thanks & regards
Lokananth