mysql query performance issue

Discussion in 'MySQL' started by gilbertsavier, Jul 8, 2009.

  1. gilbertsavier

    gilbertsavier New Member

    Joined:
    Jun 25, 2009
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    You also should analyze the table structure, creating indexes on the required fields will help.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice