[Betternestedset-talk] patch to add update conditions

[Krishna Dole]

Hi Christian,

Thanks for submitting a patch.

Are you seeing significant performance gains with this patch? Have you
benchmarked performance with different index setups? I'm reluctant to
commit a performance patch without good benchmarks. I've personally
seen unexpected results from what I thought would be straightforward
performance improvements (adding indexes just slowed the updates down
because the indexes needed to be refreshed on update, extra conditions
in the where clause didn't help, etc.).

best,
Krishna

On 7/6/07, Christian Niles <christian at unit12.net> wrote:
> Hi All,
>
> I've written (and attached) a patch that adds additional WHERE
> conditions to the UPDATE statement used in the move_to method.
>
> The conditions added are the same as those in the various CASE
> clauses, but by duplicating them in the WHERE clause, the database
> can take advantage of indices on the table. The previous UPDATE
> statement, without the extra conditions, would end up iterating
> through the entire tree. I assume it was smart enough to notice when
> a row wasn't actually changed, but for a tiny update of a large tree,
> it adds up.
>
> FYI, I have a multi-column index on my table, which includes the
> scope column, as well as the left and right columns. E.g.:
>
>         add_index :tree_items, [:scope_id, :lft, :rgt]
>
> I've tested using PostgreSQL, so it might be nice to hear from
> someone using MySQL and other databases.
>
> thanks!
> christian.
>
>
> _______________________________________________
> Betternestedset-talk mailing list
> Betternestedset-talk at rubyforge.org
> http://rubyforge.org/mailman/listinfo/betternestedset-talk
>
>
>