MantisBT - TestLink
View Issue Details
0003433TestLinkDatabase Generalpublic2010-05-05 22:582012-06-05 21:07
1.9 Beta 3 
0003433: get_by_name method of testcase class should not return external id
Returning the tc_external_id field from the tcversions table in the get_by_name method from the testcase class makes the response time much slower.

Benchmarks on our system running MySQL shows that on average, the current implementation of get_by_name takes about 65ms to complete. After removing tcversions and tc_external_id from the query, it takes less than 1ms.

The only place the code seems to rely on tc_external_id being returned from get_by_name is in the test suite: lib/api/test/TestlinkXMLRPCServerTest.php. Also, the getExternalID method in the testcase class can be uised to get this value if required.
I've uploaded a diff text file with the proposed change.
No tags attached.
txt diff.txt (1,049) 2010-05-05 22:58
Issue History
2010-05-05 22:58rtessierNew Issue
2010-05-05 22:58rtessierFile Added: diff.txt
2010-05-05 23:57fmanNote Added: 0009919
2010-05-07 04:14rtessierNote Added: 0009953
2012-06-05 21:07dsoederNote Added: 0016840

2010-05-05 23:57   
To undertans this figures is important to know DB size.
We can think about adding some option to get less info in order to improve performance, but BLINDLY removing this column is not IMHO the solution
2010-05-07 04:14   
Our database includes thousands of testcases and millions of executions.
2012-06-05 21:07   
Apologies in advance for issue necromancy. I've taken over from rtessier in maintaining the testlink instance this issue was found on. Given its age I'd be fine with closing this issue.

I do have one comments though:
Based on the supplied diff a likely cause could be the fact the query is carrying out a cross join with a WHERE clause to pare down the results instead of a smaller join.

Relevant docs: [^]
INNER JOIN and , (comma) are semantically equivalent in the absence of a join
condition: both produce a Cartesian product between the specified tables (that
is, each and every row in the first table is joined to each and every row in
the second table).