MySQL 5 - Mysql query performance issue

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

Live chat By miOOt

To even consider tuning the query please post the EXPLAIN of the query, the CREATE statements of the tables being used in the query (SHOW CREATE TABLE) and the SHOW INDEX for each table in the query.