Mysql query performance issue

i All,

I am using testlink tool with mysql ( ).

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

NHB.parent_id AS testsuite_id, NHA.parent_id AS tc_id,
NHB.node_order AS z, T.tcversion_id AS tcversion_id, AS feature_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
nodes_hierarchy NHA JOIN nodes_hierarchy NHB ON NHA.parent_id =
JOIN testplan_tcversions T ON = T.tcversion_id
JOIN tcversions TCV ON =
JOIN executions E ON ( = 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 =
WHERE T.testplan_id=30397
AND (UA.type=1 OR UA.type IS NULL)
AND E.status=‘p’
AND 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, ASC;

Any help to bring down the sql execution time would be highly appreciated.