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

{prefix}context path index is pretty much useless

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 1.9.9
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      PostgreSQL database / UTF-8
    • Database:
      PostgreSQL
    • Affected Branches:
      MOODLE_19_STABLE

      Description

      If a PostgreSQL Moodle database is running with UTF-8 encoding/locale settings, then the default index on the

      {prefix}

      context table's path column is mostly useless, as the majority of queries on that column use a LIKE operator. My understanding is that UTF-8 databases are either required or strongly recommended for Moodle, so this will pretty much impact all PostgreSQL Moodle instances.

      To enable this index to work as (I assume) expected, the index should be created with explicit use of the varchar_pattern_ops opclass. The relevant PostgreSQL syntax is:

      CREATE INDEX mdl_con_pat_ix_patternops ON mdl_context (path varchar_pattern_ops);

      I suggest that the original index, without the pattern op class, is not really required and would provide more overhead than performance benefit so should probably be replaced rather than running both.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: