Announcement

Announcement Module
Collapse
No announcement yet.

mysql query performance issue

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Please post explain for that query.
    Quote:


    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.

    Comment

    Working...
    X