search

andrewhancox / moodle-filter_translations

7 stars 8 forks source link

Slow queries when fetching records from translation issues table #148

Open rjnl opened 1 year ago

rjnl commented 1 year ago

We are getting occasional "Unable to obtain session lock" errors on our site using PostgreSQL as the database.

The following query seems to be quite expensive and it gets called several times. SELECT * FROM mdl_filter_translation_issues WHERE url = $? AND md5key = $? AND targetlanguage = $? AND contextid = $? AND generatedhash = $? AND issue = $? AND translationid = $?

The condition is checking against almost all fields/columns in the translation_issues table.

This is called here: https://github.com/andrewhancox/moodle-filter_translations/blob/417f167d2c8069766d4b401c6085afb0d0edb780/classes/translator.php#L169

andrewhancox commented 1 year ago

Definitely a missing index - I would assume indexing translationid on filter_translation_issues table would probably be enough to solve it but would be worth playing with EXPLAIN to confirm.