Moodle
  1. Moodle
  2. MDL-34159

Performance improvement for where_clause_list

    Details

    • Rank:
      42494

      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.

        Activity

        Hide
        Petr Škoda added a comment -

        fixing, thanks

        Show
        Petr Škoda 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 Škoda added a comment -

        Thanks for the report.

        Show
        Petr Škoda added a comment - Thanks for the report.
        Hide
        Petr Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda 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: