Updating hierarchy slow on mysql

[joevandyk]

delete FROM `fc_location_hierarchies` WHERE descendant_id IN (SELECT DISTINCT descendant_id FROM (SELECT descendant_id FROM `fc_location_hierarchies` WHERE ancestor_id = 46179 OR descendant_id = 46179) AS x);
Query OK, 0 rows affected (0.16 sec)

this query runs when updating the closure tree hierarchy. It's taking a while on mysql (160ms), with no matching hierarchy rows to delete -- and less than 200k rows in the hierarchies table.

I made a mysql-specific query here: Crowd-Cow@712de3f#diff-693c6ab46fe400dd004a79a24ab8d56b
that speeds up the update of the hierarchy tree from 100ms to under 1ms.

Thoughts? Obviously, this would mean that we would have different SQL for different databases.. but the performance was bad enough on mysql that we had to fork.

We're using msyql 5.7.

[shopshow]

facing the same issue, really slow when updating hierarchy on large table.

[kbrock]

facing the same issue, really slow when updating hierarchy on large table.

this will be available at initialization time, so a database specific module could be included/extended as part of the has_closure_tree call

[timdown]

I did start working on this a few months ago but I refactored a bunch of stuff and it got away from me. I might have another go. In the meantime, the application I use closure_tree in has a monkey-patched version of the gem with the faster MySQL hierarchy deletion, which has removed the urgency for me.