The following two enhancements were reported on the list by Lindsay Evans based on her experience with tables larger than 2 million rows.
First:
Just noticed that move_to_child_of was running pretty slow (around 20
seconds) on a table with about 2.2 million rows, but was only updating
about 13000 records.
I've looked at the SQL move_to produces, and it seems to be doing a
full table scan in it's update, so I made this change to limit it to
only the rows that would be updated (sorry for no patch, my copy of better_nested_set.rb is pretty messy at the moment from debugging):
update_scope_condition = " AND ((#{left_col_name} BETWEEN #{a} AND #{b}) OR (#{left_col_name} BETWEEN #{c} AND #{d}) OR (#{self.class.primary_key} = {self.id}))"
base_set_class.update_all([existing SQL], scope_condition + update_scope_condition
And secondly:
I've recently started using better nested set on a project, and found
that the performance was rather horrible on an InnoDB table with ~2.2
million rows (~27 seconds on a self_and_ancestors call, set is about
5 levels deep)
After a bit of digging I discovered that MySQL was ignoring the
indexes I'd set on the lft & rgt columns when using the BETWEEN
operator.
According to the MySQL docs, between is equivalent to (min <= expr AND
expr <= max) <http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between>,
after replacing all the instances with this syntax, the query time
went down to ~0.5sec, quite an improvement :)
