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

    Details

    • Database:
      MySQL
    • Testing Instructions:
      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.
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_24_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      wip-MDL-35961-master

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              abgreeve Adrian Greeve
              Reporter:
              ppollet Patrick Pollet
              Peer reviewer:
              Frédéric Massart
              Integrator:
              Dan Poltawski
              Tester:
              Michael de Raadt
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                3/Dec/12