Details
-
Bug
-
Resolution: Fixed
-
None
-
Critical
-
Empty show more show less
Description
problem 1
The SegmentMap generation was not filtered with the taskGuid of the current task. Depending on how many VisualReview projects in the database this is a painful performance problem.
In one customer installation the affected table rows were reduced from ~200000 to 3000 affected rows.The result in the browser is an improvement from 3,8MB in 30 seconds to 25kb in 170ms.
This could solve probably a lot of performance problems with VisualReview depending on the data amount in the DB. I assume that the JS engine would have to work with the 3,8MB result too.
problem 2
There were no indices set on the segment table on the page field and on the views on the segmentNrInTask.
For both fields an index makes sense. The explain is changing from without index:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | LEK_segment_view_e8aa38e02bbf5cb21a664da6b3911424 | ALL | NULL | NULL | NULL | NULL | 5781 | |
1 | SIMPLE | LEK_visualreview_segmentmapping | ref | taskGuid | taskGuid | 116 | const | 10454 | Using where |
to with index:
1 | SIMPLE | LEK_visualreview_segmentmapping | range | taskGuid,taskGuid_2 | taskGuid_2 | 239 | NULL | 186 | Using where |
1 | SIMPLE | LEK_segment_view_e8aa38e02bbf5cb21a664da6b3911424 | ref | segmentNrInTask | segmentNrInTask | 4 | worldtransTranslate5Net.LEK_visualreview_segmentmapping.segmentNrInTask | 1 |
according to the explain the result is much better again.
BUT: this does not explain why the request sometimes was running in 0.01 seconds and sometimes in more then 30 seconds. MySQL caching probably?