Here's my information on the problem, in case it helps someone.My situation
I host over 40 Joomla sites that I've been upgrading from 1.0.15 via 1.5 to 2.5.1. After last week migrating the sites with >1000 articles, my server got really
overtaxed because of this query:
FROM j17_assets AS a
LEFT JOIN j17_assets AS b ON b.lft <= a.lft AND b.rgt >= a.rgt;
For few sites the execution of that single query took over a minute, with MySQL process "Copying to tmp table". The query is apparently caused by a recursive query in libraries/joomla/access.php
around line 187.
Though the problem doesn't seem to appear very consistently, I did manage to find conditions that replicate it.
It's worth nothing that logging in as the Super User does not
trigger the problem (assumedly because since super user has full access Joomla skips the acl check), but logging in as a user with lesser priviledges can
trigger it. Accessing user control from the admin interface also seems to trigger it. I am not sure if this is only in my conditions or if is more general behaviour.Fixing the problem
While tracing the problem I noticed the assets table seems broken, and ended up writing a script that recreates the tree structure as best as it understands, and then reassigns new parent_id,lft,rgt,level values for all the rows.
At least my script fixes the problem. You can get it from here:GitHub / ikajaste / Joomla-2.5-Asset-Weaver
(Use the ZIP link at left side to download the code, and run it as command line php
. Also read the README.)
I would guess the instructions at Fixing the assets table
also fix the problem, but it is pretty tiresome to execute on site with 92 categories, not to mention a system with over 40 sites, each having at least over 6 categories...
The fix_assets.php might also fix the problem, I haven't tried it yet. Will update this article if I get around to trying it. First, I have those 40 sites to fix.
Note that the even on a fixed database the query still takes the same time, but the apparently fixing the database causes the query not to be executed by the Joomla code without more sepefic limitations to it.
... but as to why any sane migration process would let something as crappy as a half-broken assets table through, I cannot say...