Mosets Tree – An Extremely Slow/Unoptimized Joomla Extension

One of our customers complained to us today that his Joomla website was extremely slow. So, we used the MySQL slow query log to unveil the issue, and we discovered that the culprit was Mosets Tree. In particular, it was these two queries:

1. SELECT l.*, c.cat_name, c.cat_id, u.username AS username, u.name AS owner, u.email AS owner_email, img.filename AS image FROM (wq8k0_mt_links AS l, wq8k0_mt_cl AS cl, wq8k0_mt_cats AS c) LEFT JOIN wq8k0_users AS u ON u.id = l.user_id LEFT JOIN wq8k0_mt_images AS img ON img.link_id = l.link_id AND img.ordering = 1 WHERE link_published='1' && link_approved='1' && link_featured='1' AND img.img_id > 0 AND ( publish_up = '0000-00-00 00:00:00' OR publish_up <= '2013-05-31 01:09:59' ) AND ( publish_down = '0000-00-00 00:00:00' OR publish_down >= '2013-05-31 01:09:59' ) AND l.link_id = cl.link_id AND c.cat_id = cl.cat_id AND cl.main = 1 ORDER BY link_featured ASC

2. SELECT cf.*, cfv.link_id, cfv.value, cfv.attachment, cfv.counter, ft.ft_class FROM wq8k0_mt_customfields AS cf LEFT JOIN wq8k0_mt_cfvalues AS cfv ON cf.cf_id=cfv.cf_id AND cfv.link_id = 152 LEFT JOIN wq8k0_mt_fieldtypes AS ft ON ft.field_type=cf.field_type WHERE cf.published = '1' ORDER BY ordering ASC

The first query was taking 25 seconds to execute, while the second was taking about 30 seconds. So we checked these two queries in phpMyAdmin using the Explain command (e.g. Explain [query]), and we discovered that not one single field was indexed – not one! This means that any Select query will take ages to execute if there’s a lot of data (and our customer had a lot of data). Not only that, we also discovered that not a single table had a primary field, which means any Join between the Mosets‘ tables is extremely expensive on the system.

We were surprised, because Mosets Tree is a highly used Joomla extension that is commercial! For a commercial extension, one usually expects a high quality extension. Unfortunately, we can’t say that about Mosets Tree.

Obviously, solving the problem meant that we had to manually index many fields belonging to the Mosets‘ tables. Once we did that, the site became much faster and the load on the server decreased.

After fixing the problem, we started thinking, how many famous extensions are similarly unoptimized? How many Joomla websites out there suffer because they are using one of these extensions? And what did the developers have in mind when they completely ignored something so obvious (which is indexing their fields)? One has to wonder whether database optimization was left out on purpose (especially when an extension is many years old)…

Now, if you’re reading this post because your website is extremely slow because of Mosets Tree, then we suggest that you index the filter fields in all the Mosets Tree tables (this can be done in phpMyAdmin). If you don’t know how to do that, then we suggest that you contact us. We can optimize this extension for you in not time and at a very affordable price. All you need to do is to give us a call or shoot us an email!