Moodle
  1. Moodle
  2. MDL-34159

Performance improvement for where_clause_list

    Details

      Description

      In Moodle 1.9, the database *_list() methods used IN, but in Moodle 2.X, they use OR. We have noticed that when using a very large list of items (EG: ~500), that ORs are slower than using an IN. This might also be true for other scenarios (EG: fewer list items, but much larger table, etc)

      Suggested changes:

      • Instead of using OR, use IN. There is one trick here though and that's NULLs. So, if there is a NULL value, the end result should probably look like: ($field IS NULL OR $field IN(...))
      • Make sure the values are unique and there are no duplicates.

        Gliffy Diagrams

          Activity

          Hide
          Petr Skoda added a comment -

          fixing, thanks

          Show
          Petr Skoda added a comment - fixing, thanks
          Hide
          Kris Stokking added a comment -

          We are seeing roughly a 90% speed-up in SQL execution time on MySQL 5.5 by switching from OR's to IN's for very large sets of data. This is true for both indexed and non-indexed fields. There is no change in the output of EXPLAIN.

          Show
          Kris Stokking added a comment - We are seeing roughly a 90% speed-up in SQL execution time on MySQL 5.5 by switching from OR's to IN's for very large sets of data. This is true for both indexed and non-indexed fields. There is no change in the output of EXPLAIN.
          Hide
          Petr Skoda added a comment -

          Thanks for the report.

          Show
          Petr Skoda added a comment - Thanks for the report.
          Hide
          Petr Skoda added a comment -

          I have decided to not add array_unique there because I was not sure about performance, memory costs and handling of invalid values - it was always the responsibility of the caller.

          Show
          Petr Skoda added a comment - I have decided to not add array_unique there because I was not sure about performance, memory costs and handling of invalid values - it was always the responsibility of the caller.
          Hide
          Mark Nielsen added a comment -

          Hi Petr, thanks for quick response!

          I checked out your code and do you think it would be wise to run array_unique in $params? Also, was there a reason why you didn't run $params through get_in_or_equal?

          Show
          Mark Nielsen added a comment - Hi Petr, thanks for quick response! I checked out your code and do you think it would be wise to run array_unique in $params? Also, was there a reason why you didn't run $params through get_in_or_equal?
          Hide
          Petr Skoda added a comment -

          1/ array_unique was never used there - as I said it was the responsibility of the caller to make it unique
          2/ hmmm, get_in_or_equal is sure better there!

          Show
          Petr Skoda added a comment - 1/ array_unique was never used there - as I said it was the responsibility of the caller to make it unique 2/ hmmm, get_in_or_equal is sure better there!
          Hide
          Petr Skoda added a comment -

          resubmitting, I have commended about the use of get_in_org_equal - it can not be used there because it does not work with bools and nulls

          Show
          Petr Skoda added a comment - resubmitting, I have commended about the use of get_in_org_equal - it can not be used there because it does not work with bools and nulls
          Hide
          Mark Nielsen added a comment -

          Sorry, my page must have been out of date and I didn't see your comment about array_unique.

          Regarding get_in_or_equal, I meant using it at this point (URL). If not, that's fine, I'm really happy with the code as is (probably faster without get_in_or_equal)! Thanks so much!

          Show
          Mark Nielsen added a comment - Sorry, my page must have been out of date and I didn't see your comment about array_unique. Regarding get_in_or_equal, I meant using it at this point (URL) . If not, that's fine, I'm really happy with the code as is (probably faster without get_in_or_equal)! Thanks so much!
          Hide
          Petr Skoda added a comment -

          thanks a lot for the feedback, let's hope integrators will like it too and will integrate it soon, ciao.

          Show
          Petr Skoda added a comment - thanks a lot for the feedback, let's hope integrators will like it too and will integrate it soon, ciao.
          Hide
          Dan Poltawski added a comment -

          It really does beg the question why the mysql isn't doing this itself internally!

          Show
          Dan Poltawski added a comment - It really does beg the question why the mysql isn't doing this itself internally!
          Hide
          Dan Poltawski added a comment -

          Integrated, thanks

          Show
          Dan Poltawski added a comment - Integrated, thanks
          Hide
          Dan Poltawski added a comment -

          Passed on mssql and oracle (although I did find some unrelated issues)

          Show
          Dan Poltawski added a comment - Passed on mssql and oracle (although I did find some unrelated issues)
          Hide
          Dan Poltawski added a comment -

          Congratulations!

          You've made it into the weekly release!

          Thanks for your contribution - here are some random drummers to keep you inspired for the next week!
          http://www.youtube.com/watch?v=_QhpHUmVCmY

          Show
          Dan Poltawski added a comment - Congratulations! You've made it into the weekly release! Thanks for your contribution - here are some random drummers to keep you inspired for the next week! http://www.youtube.com/watch?v=_QhpHUmVCmY

            People

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

              Dates

              • Created:
                Updated:
                Resolved: