Affects Version/s: 1.9.8
Fix Version/s: None
Component/s: Database activity module
Environment:Apache + MySQL (MyISAM) running on server with 2 x Quad-Core Xeon, 4 GB RAM, Ubuntu Server edition 8.04 LTS
In a database activity with many entries (approx. 1200), performance was very low for e.g. the list view to be displayed. Pages needed up to 5 seconds to be generated, causing 100% load on one CPU core, on an otherwise idle server.
The problem turned out to be caused by queries of the following type:
SELECT COUNT(DISTINCT c.recordid) FROM mdl_data_content c,mdl_data_records r,mdl_data_content cs, mdl_user u WHERE c.recordid = r.id
AND c.fieldid = 32
AND r.dataid = 10
AND r.userid = u.id
AND cs.recordid = r.id
More precisely, there is no usable index for the "cs.recordid = r.id" join, which leads to full table scans.
Adding an index in the "mdl_data_content" table, on the "recordid" column, improved performance dramatically.
|Field||Original Value||New Value|
|Workflow||jira [ 36347 ]||MDL Workflow [ 46231 ]|
|Workflow||MDL Workflow [ 46231 ]||MDL Full Workflow [ 74534 ]|
|Link||This issue has been marked as being related by MDL-17327 [ MDL-17327 ]|
|Status||Open [ 1 ]||Closed [ 6 ]|
|Resolution||Duplicate [ 3 ]|