Moodle

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.9.4
• Fix Version/s: None
• Component/s: Database SQL/XMLDB, Performance
• Labels:

  None

We have a good-sized Moodle installation, with 400 classes and a mysql database of ~ 1GB.

I noticed recently that the moodle forum cron is executing two database queries that join without an index, with each taking 5-10 seconds on our production server. The queries are used for cleaning old records from the forum_read table and seem to run once-per-day.

The queries are called in /mod/forum/lib.php, forum_tp_clean_read_records():

SELECT MIN(fp.modified) AS first FROM {$CFG->prefix}forum_posts fp
JOIN {$CFG->prefix}forum_read fr ON fr.postid=fp.id;

DELETE FROM {$CFG->prefix}forum_read WHERE postid IN
(SELECT fp.id FROM {$CFG->prefix}forum_posts fp WHERE fp.modified >= $first AND fp.modified < $cutoffdate)";

These queries are slow because there is no index on the postid field from the mdl_forum_read table. So the db ends up scanning (in our case) half a million rows. Adding the missing index speeds the query times significantly... the queries now take about 0.4 seconds.

In mysql, you can add the missing index with this command:
create index mdl_forum_user_postid_idx on mdl_forum_read (postid);

-Garret

• Assignee:

  Eloy Lafuente (stronk7)

  Reporter:

  Garret Gengler

  Participants:

  Eloy Lafuente (stronk7), Garret Gengler

• Created:

  19/Mar/09 12:25 AM

  Updated:

  28/Mar/09 2:22 AM