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.

Below is the link for the testlink topic
teamst.org/phpBB2/viewtopic.php?t=1938

Please post explain for that query.

[B]Quote:[/B]

explain 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, …

There should be a table in the answer:
id select_type table type possible_keys key key_len ref rows Extra

That table is very important and it will show which mysql table needs tuning.

Edit:
You could also post table structure from every table displayed in the “table” column.