Speed up Propel nested-set inserts building large tree

1
votes

I'm using Propel 1.6.7-dev, and am maintaining 2 trees in a nested set (each with a different scope column). Currently, we have about 20K items in each set (so ~40k records total).

I can rebuild the database at will, and periodically do (to handle certain large-tree changes).

Right now, this is taking about 50m or so to accomplish, and I'm wondering how I might be able to speed it up (if this doubles in size, the time might become too large rebuild regularly).

Right now, I'm:

  • clearInstancePool();
  • Propel::disableInstancePooling();
  • beginTransaction();
  • (code to re-insert all items into the 2 trees)
  • commit();
  • enableInstancePooling();

I've experimented with changing the engine from MyISAM to MEMORY, build tree, then convert back to MyISAM. However, I run out of memory:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in vendor/propel/runtime/lib/adapter/DBAdapter.php on line 433

Any suggestions?

1
phppropel
@halfer -- It was the indexing on left/right. I never thought of that. Submit this as the answer, please. – Mike Crowe
No probs Mike, thanks and done. What speed-up did you get in the end? – halfer

1 Answers

0
votes

The nested set algorithm relies heavily on WHERE clauses on "left" and "right" columns, to maintain the correct structure of your tree. If your queries are getting progressively slower as you have more nodes, the solution may be to add indexes on these columns.