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

Strange SQL in mod/data/view.php table data_content is joined twice in the main SQL requests

XMLWordPrintable

    • MySQL
    • MOODLE_19_STABLE, MOODLE_23_STABLE
    • MOODLE_24_STABLE
    • wip-MDL-35961-master
    • Hide

      Warning! This needs to be tested on all databases.

      1. Create a database activity.
      2. Add fields and save the template, or alternatively use the preset and import some entries.
        • (Please note with the csv file that it contains 500 entries. This may take some time to import. Feel free to remove records to make this process faster)
      3. Do a few simple searches on the database.
        Check to make sure that there are no errors displayed.
        Ideally you would check to make sure that the right number of entries are returned.
      Show
      Warning! This needs to be tested on all databases. Create a database activity. Add fields and save the template, or alternatively use the preset and import some entries. (Please note with the csv file that it contains 500 entries. This may take some time to import. Feel free to remove records to make this process faster) Do a few simple searches on the database. Check to make sure that there are no errors displayed. Ideally you would check to make sure that the right number of entries are returned.

      Back in Moodle 1.9.x, we noticed that the list display of a database with several thousand of entries was unbearably slow (more than one minute)

      We noticed that in the two main SQL queries, the table data_content was joined TWICE as below :

      $tables = '

      {data_content} c,{data_records} r, {data_content}

      cs,

      {user}

      u ';

      We fixed it by removing the second alias cs in two places ...and got back to less than one second of processing

      By peeking to code of the latest Moodle 2.3.2, we noticed that the table data_content is still joined twice, which looks quite surprising ? We do not have yet huge database activities in Moodle 2.x but it seems that somebody should have a look to these queries.

      I am attaching the patch I have applied to mod/data/view.php for Moodle 2.3.2

      Cheers

        1. mdl-35961-1-500.csv
          65 kB
        2. mdl-35961-preset.zip
          4 kB
        3. mod_data_view.patch
          4 kB

            abgreeve Adrian Greeve
            ppollet Patrick Pollet (Inactive)
            Frédéric Massart Frédéric Massart
            Dan Poltawski Dan Poltawski
            Michael de Raadt Michael de Raadt
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.