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(...))

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.

Kris Stokking
added a comment - 04/Jul/12 12:42 AM 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.

Petr Skoda
added a comment - 04/Jul/12 1:36 AM 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.

Mark Nielsen
added a comment - 04/Jul/12 1:41 AM 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?

Mark Nielsen
added a comment - 04/Jul/12 2:20 AM 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!

Dan Poltawski
added a comment - 12/Jul/12 5:59 PM 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