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

Remove all attempts to cast sesskey to BINARY as it was causing MySQL to ignore the preferred index, resulting in slow response time

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.8, 1.8.1, 1.8.2, 1.8.3, 1.8.4, 1.8.5, 1.8.6, 1.8.7, 1.8.8, 1.9, 1.9.1, 1.9.2, 1.9.3, 1.9.4
    • Fix Version/s: None
    • Component/s: Libraries, Performance
    • Labels:
      None
    • Database:
      MySQL
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE

      Description

      Our team (at SFSU) talked to the MySQL support (we have subscribed to their enterprise level support) and they suggested that we look into the codes and remove all attempts to cast sesskey to BINARY, as it will cause MySQL to ignore the preferred index and not use an index at all, resulting in slow response time. So we dig into the codes and did the following changes

      in /lib/adodb/session/adodb-session.php (and adodb-session2.php in the same directory)

      replace

      $binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : '';

      to

      $binary = $conn->dataProvider === 'mysql' ? '' : '';

      This has dramatically improved the performance on our production site. However, would like to hear people's opinion about this change (also emailed Martin D, Martin L, and the author of ADODB already, but haven't heard back from them yet). Maybe the core could consider this change too. Also see the discussion thread that our system administrator Taylor posted here for more details:

      http://moodle.org/mod/forum/discuss.php?d=106108

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  11 Vote for this issue
                  Watchers:
                  14 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: