[Betternestedset-talk] MySQL performance with large tables

Lindsay Evans lindsaye at gmail.com
Thu Feb 15 22:32:46 EST 2007

Hi all,

Just joined the list, I checked through the archives & tickets, but
couldn't find anything similar.

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
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 :)

This has been working fine for me so far, but it would be great to
hear others opinions on whether this will cause any other problems.

I've attached a patch if anyone wants to check it out for themselves.
I haven't tested this very extensively apart from in my app, just ran
the unit tests & they all passed (after updating the test_sql_for to
use the same syntax).
I've also only tested this with MySQL 5, so I have no idea how these
changes would effect other databases.

Lindsay Evans
-------------- next part --------------
A non-text attachment was scrubbed...
Name: better_nested_set.rb.diff
Type: application/octet-stream
Size: 6686 bytes
Desc: not available
Url : http://rubyforge.org/pipermail/betternestedset-talk/attachments/20070216/abf328cb/attachment.obj 

More information about the Betternestedset-talk mailing list