Slow queries with move_to

[patfrat]

Hello,
I have slow queries with the move_to function.
specially with the function base_set_class.update_all on line 956 !
how can i optimise this ?
I have 1492 items on my database using parent_id, lft, rgt fields with your plugin and queries take between 3s to 6s to perform the move_to function ...
thank you in advance

[chris]

Are you using "move_to_child_of"? That sounds extremely slow. Do you have database indexes on lft, rgt, and parent_id? My table that uses this has over 6000 items in it, and even on my slower development system setup, a move_to takes tens of milliseconds. I'm using MySQL on MacOS X and Linux.

[patfrat]

Hello,
Yes, i have lft,rgt, parent_id ... and i use move_to_child_of
I use it to place files uploaded to the tree of my application that is like a Document management system ...
i used newrelic plugin to see where was the slow query and this is exactly at line 956 of the plugin ...

[chris]

I don't know why it's that slow for you. That's a complex SQL call on that line, but you'll have to dig into it and see what could be done. I'm still surprised it takes that long, but given you mention it's images, is it possible that whatever you're using for the images is say re-generating thumbnails or anything like that when you do a save/update on it? Given that it's fractions of a second for me to do that, it makes me suspicious of the model objects you're using with it.

As other info: I'm not the original author of this plugin, I just put it on GitHub, added a few tweaks, etc. So, I don't have a lot of depth on the actual implementation. I'd look at your logs and so on and see what all is going on during that. RPM is a great tool (I use it as well), but you probably need to look at a more fine grained view from your logs to see what all is causing that SQL to be so slow (and whether it's truly that one line of SQL, or whether other things happen as a result of callbacks (e.g. before/after hooks, etc.) and whether the time of those is aggregated into that in RPM, etc.

[patfrat]

Ok, thanks, i will investigate more on my model and my database to see what is slow when i save and move_to_child_of my datas ... and i will return my results here if i find a solution !