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.

[Izzmo]

Well, you are joining a lot of tables together.. and that's your main problem.

What exactly are you trying to do with this query? (And what type of information are you getting from it?? text.. binary data, etc)