We’re not big fans of the #__assets table, but you probably know this already if you’re one of our loyal readers! The #__assets table makes everything on the website slower, and not to mention that minor data integrity issues on that table can lead to large problems on the Joomla website.
But let’s focus in this post on the performance issues caused by the #__assets table, and what to do about them. In short, when you add a new content entry (an article, a category, a module, etc…), Joomla has to update a large chunk of the rows in the #__assets table to insert the new row, which will increase the load on the server. So, the more rows you have in your #__assets table, the more time you’ll have to wait when you save a new article.
If you want proof that the #__assets table is the bottleneck, then here’s an example from the MySQL Slow Query Log from one of the websites that we manage:
# Time: 141212 12:57:49
# User@Host: database-user[database-name] @ localhost [] Id: 282378
# Query_time: 5.492695 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 2303
use database-name;
SET timestamp=1418252269;
UPDATE #__assets
SET lft = lft + 2
WHERE lft > 648;
The query above took nearly 5.5 seconds and it happened when our client added a new article to their website. That client usually adds about 70 articles/day – which means that they have 70 of these slow queries every day. And it gets worse, since the larger the table is, the more time these queries will take and the more the load there will be on the server (the 5.5 seconds occurred on a table that only had 2,300 rows – imagine what would have happened if that table had 100 times that number of rows – content managers will have to wait 10 minutes after clicking that “Save” button!).
So, what do about this?
Well, the solution is simpler than you can possibly imagine. All you need to do is to regularly delete those rows (in the #__assets table) that are created by adding new articles! This can be done by executing the following query in phpMyAdmin (make sure you backup your database before executing the below query and also make sure you optimize the table after executing the query):
DELETE FROM #__assets WHERE `name` LIKE '%com_content.article.%';
We know, you probably think that we have gone insane and that we’re trying to destroy your website, but this is not the case. These entries have no purpose whatsoever and they are not needed for your website to work. You can really delete them, and once you do, you will notice a substantial drop in your server’s load during working hours and your new articles will save instantly.
Now, you may be wondering, will there be any side effects?
We regularly clean the #__assets table of our major clients, and so far, we haven’t seen a single glitch caused by it. But, if you’re afraid to do the above yourself, then a good idea would be to contact us, we’ll do it for you in no time (we’ll also create a cron job to automatically clean the #__assets table for you at regular intervals) and for a very affordable fee!
Hi, I tried the query but I get the following error:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
I’m running joomla 3.3.6 on mysql 5.5.42-cll
Can you please update the query included in this article?
Thank you!
Hi David,
The SQL query definitely works – we actually use the same exact query on our clients’ sites. Check whether you are adding a quote somewhere or you are missing a quote or you are using a different type of quote than the one in the article.
Hi, thanks for your fast response. I just copy and paste the query from here directly to Run SQL query/queries on phpMyadmin. Is there something else that could be wrong on my side?
Thank you
Hi Fadi, you were right, I was doing it wrong. This time it worked perfectly.
Thanks a lot!
Doesn’t have any effect on the performance of my website. When I save an article I still have to wait very Long. In phpMyAdmin I see these processes:
UPDATE #__content SET ordering = 453 WHERE `id` = '8990'When I press F5 to refresh the page than another one:
UPDATE #__content SET ordering = 1858 WHERE `id` = '2436'The ordering of the ID’s goes from the maximum down, and that’s the reason why I must wait so long.
Hi Tom,
That’s actually a different issue – your problem typically happens when you’re ordering articles in the backend (if it does happen elsewhere, then possibly you have a rogue extension on your website).
Hopefully we will discuss your issue in a future post. Stay tuned!
EDIT:
It works after clear the cache
OK – I was wrong. It’s not working for me.
Knowing how often this article is referenced by this team on the Joomla forum, I feel it important to repeat what I’ve said to them on the forum and Twitter here several times.
While this article does a good job explaining the root issue and how this fix can improve things, there are a couple of important things to note here.
1) It leads to possible corruption of the nested tree model used by the assets table. In theory this should never happen because you are deleting children of a node so a full rebuild process should only close the gap left in the tree node values (the lft/rgt columns on the table), but there is always an odd chance of something going wrong along the way and fixing a corrupt nested tree table is a major pain (I’ve done it on a couple of projects in the last week, it is definitely not fun).
2) If you have custom ACL rules defined at the category or article level, there is a good chance that those rules may not apply at all and the ACL checks will revert to either the component or global levels (this part I am not 100% certain of, I’ve personally not studied the ACL code and how it determines inheritance or what values it uses for a non-existing asset). For the purposes of viewing your site, this makes no difference at all. But if you are on a website where you have users that have restricted editing capabilities (i.e. a blog where writers can only create and edit content in specific categories), this could cause their permissions to not work correctly.
Keeping point 1 in mind, generally on sites where there are only a couple of admin users at the most or don’t make custom configurations which could cause point 2 to be a concern, this generally is a safe action to take. I don’t want to discourage anyone from considering it. But, as with any change in your site’s data structures, it is not risk free and I would only suggest that you are aware of this risk before taking any action.