Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-4667

Change the column order of the mdl_log index 'courseuserid'

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Trivial
    • Resolution: Fixed
    • Affects Version/s: 1.5.4
    • Fix Version/s: 1.9
    • Component/s: General
    • Labels:
      None
    • Environment:
      All
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_15_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      The log table usually has the most rows in it and can be very large. In the site level user logs Moodle does queries like SELECT COUNT FROM mdl_log l WHERE l.userid='1'.

      Usually these kind of queries are quite fast, but with very large tables they start to get slower too, if the WHERE column is not indexed.

      MySQL can't use the index 'courseuserid' very efficiently for the WHERE userid=id type of queries. From MySQL docs: If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

      Now, there is already an index 'coursemoduleaction' in this table which can be used to match any WHERE course=id queries. Changing the index 'courseuserid' to 'useridcourse' makes no difference in size and for queries that hit both userid and course, but that would give a big boost to those WHERE userid=id queries...

        Attachments

          Activity

            People

            Assignee:
            skodak Petr Skoda
            Reporter:
            samulik Samuli Karevaara
            Tester:
            Nobody
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              3/Mar/08