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

    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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

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

                  Dates

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