About a month ago, I decided to truly see how efficient Joomla's SQL was...  It turns out, it's PRETTY good, but can use some improvement.  I logged all queries that were not using an index, then ran describe.  I came up with a set of queries (adding indexes to tables) that appears to speed up Joomla by about 0.1 second (on the sites I was using to measure that, it's about 25% faster).  I will be creating a directory for performance modifications (and others) for 3pd extensions, but I wanted to get this out!

NOTE:  I hold no guarantee that test queries won't screw up your database, so BACKUP FIRST!!!  ALSO, be sure to change jos_ to whatever prefix you are using... So, lets get to it...

For the Joomla core, here you go...

ALTER TABLE `jos_categories` ADD INDEX `title_id` (`title`,`id`);
ALTER TABLE `jos_components` ADD INDEX `opt_par` (`option`, `parent`, `id`);
ALTER TABLE `jos_components` ADD INDEX `admin_opt` (`admin_menu_link`, `option`);
ALTER TABLE `jos_content` ADD INDEX `state_access` ( `state` , `access` , `publish_up` , `publish_down` , `id` , `catid` , `sectionid` , `created_by` );
ALTER TABLE `jos_core_acl_aro` ADD INDEX `val_aro_id` (`value`, `aro_id`);
ALTER TABLE `jos_core_acl_aro_groups` ADD INDEX `name_lft_rgt` (`name`, `lft`, `rgt`);
ALTER TABLE `jos_mambots` ADD INDEX `fold_acc` ( `folder`, `access`, `published`, `ordering`, `element`, `params`(20));
ALTER TABLE `jos_mambots` ADD INDEX `element` ( `element` );
ALTER TABLE `jos_menu` ADD INDEX `link_id` (`link`(30), `id`);
ALTER TABLE `jos_menu` ADD INDEX `link_pub_id` (`link`(30), `published`, `id`);
ALTER TABLE `jos_menu` ADD INDEX `pub_acc_menu` (`published`, `access`, `menutype`, `parent`,`ordering`, `id`, `name`);
ALTER TABLE `jos_sections` ADD INDEX `id,acc,pub` (`id`, `access`, `published`, `name`(20));
ALTER TABLE `jos_sections` ADD INDEX `pub_acc_id` (`published`,`access`,`id`,`name`(20));
ALTER TABLE `jos_stats_agents` ADD INDEX `agent_type` (`agent`,`type`,`hits`);
ALTER TABLE `jos_templates_menu` ADD INDEX `client_menu` (`client_id`, `menuid`, `template`);
ALTER TABLE `jos_template_positions` ADD INDEX `pos` (`position`);
ALTER TABLE `jos_users` ADD INDEX `user_pass_id` (`username`,`password`,`id`);

For Community Builder:

ALTER TABLE `jos_comprofiler` ADD INDEX `hits_id` (`hits`, `id`, `user_id`);

For Docman:

ALTER TABLE `jos_docman` ADD INDEX `own_pub` (`dmowner`, `published`, `approved`, `catid`, `id`);

For Fireboard:

ALTER TABLE `jos_fb_whoisonline` ADD INDEX `userid` (`userid`, `userip`, `time`, `what`);
ALTER TABLE `jos_fb_whoisonline` ADD INDEX `user` (`user`);
ALTER TABLE `jos_fb_sessions` ADD INDEX `userid` (`userid`);
ALTER TABLE `jos_fb_users` ADD INDEX `userid` (`userid`);
ALTER TABLE `jos_fb_users` ADD INDEX `post_userid` (`posts`, `userid`);
ALTER TABLE `jos_fb_messages` ADD INDEX `id_hold` (`id`, `hold`);
ALTER TABLE `jos_fb_messages` ADD INDEX `thread_id` (`thread`, `id`, `hold`, `catid`, `time`);
ALTER TABLE `jos_fb_messages` ADD INDEX `cat_parent_hold` (`catid`, `parent`, `hold`, `id`);
ALTER TABLE `jos_fb_messages` ADD INDEX `cat_hold_`(`catid`, `hold`, `moved`, `time`);
ALTER TABLE `jos_fb_messages_text` ADD INDEX `mesid` (`mesid`);
ALTER TABLE `jos_fb_moderation` ADD INDEX `cat_user` (`catid`, `userid`);
ALTER TABLE `jos_fb_categories` ADD INDEX `pub_name_par` (`published`,`name`(20),`parent`);
ALTER TABLE `jos_fb_categories` ADD INDEX `pub_id` (`pub_access`, `id`, `name`(20));

For JomComment:

ALTER TABLE `jos_jomcomment` ADD INDEX `content_opt` (`contentid`,`option`, `published`);

For OpenSEF:

ALTER TABLE `jos_opensef_config` ADD INDEX `scope_name` (`scope`, `name`);
ALTER TABLE `jos_opensef_sef` ADD INDEX `exter_dir` (`external`, `direction`, `site_id`, `use_internal`);
ALTER TABLE `jos_opensef_sef` ADD INDEX `site_valid` (`site_id`, `valid`, `direction`, `published`, `external`, `use_internal`, `link_prio`);

For RD Glossary:

ALTER TABLE `jos_rd_glossary` ADD INDEX `cat_pub` (`catid`,`published`);
ALTER TABLE `jos_rd_glossary` ADD INDEX `state_term` (`state`,`term`,`catid`,`published`);
ALTER TABLE `jos_rd_glossary` ADD INDEX `term_cat` (`term`, `catid`, `published`);

For Versions:

ALTER TABLE `jos_versions` ADD INDEX `cat_pub_date` (`category`,`published`,`date`(10),`version_number`,`item`(10));

TrackBack URI for this entry

Comments (11)

index update
written by Nuno Zimas, January 06, 2008

Thanks for the great post.
As of now, i am just wondering if the newly created indexes have to be updated as new contents are added.

Nuno.

Oh...You da man...
written by Lance, July 07, 2007

I have a rather large site, many scripts load on the home page and I have spent many hours compessing CSS and java scripts to load faster with some decent results. So I tested the load factors on my server as well for several pages...I have a dektop version thats linked to my host called Mysql Administrator. It shows the system performance logs and I was pleasently surprised to see that your mod above did result in some noticable performance gains. The site runs fast if I were to turn off the SEO component from Artio....but the SEO URL's are an advantage that I will pay with some site performance.....GREAT JOB, Thanks.

load increased
written by Milos, July 06, 2007

This procedure dramaticly increased load on mine vps... So i needed to revert to backup.

...
written by ircmaxell, July 02, 2007

It's all SQL code. Run it in MySQL administrator (or phpMyAdmin, or command line, etc). As for duplicate Keys, Take a look at the indexes for the table, and if there isn't one similar already, just rename it (change the first `title_id` to something like `title_2`).

SQL error
written by bobthebob01, July 02, 2007

That an sql command, right. But when i tried it, it gave me the following error:
ALTER TABLE `jos_categories` ADD INDEX `title_id` ( `title` , `id` )
#1061 - Duplicate key name 'title_id'

do you know what it mean?

And i agree with bryan, this site is far more better to read with the morning coffee than C/N/N.

...
written by Andres M., July 01, 2007

.... Or is it using MySQL Administration?

How do I implement these changes in joomla core ?
written by Andres M., July 01, 2007

I would like to implement this in my sites to improve performance on joomla overall, however I am uncertain how to edit joomla core files is it joomla.php?

...
written by azrul, June 30, 2007

On the next 1.9 release of Jom Comment, a few index has been added. I added it before I came across this page, hence, added in a slightly different way but essentially the same thing. You're correct, indexing those fields will improve performance on site with large number of comment.

Always Watching
written by Bryan Rodvold, June 30, 2007

Your site is slowly turning into a "check every morning after coffee" site for me. Speeding up Joomla and its components are one of my highest priorities. Apparently it is for you as well!

Thanks for this!

Well...
written by ircmaxell, June 28, 2007

Well, I enabled logging of queries that don't use indexes in my.cnf... As far as EXPLAIN, in short you can put it in front of any query, and it'll give you some useful information... In long, if you don't know what you are doing, you can screw things up...

Process
written by Mike, June 27, 2007

Hi I was just wondering if you could go into a little more detail on the process you used to determine these changes. I have no idea what describe is, but I'd love to be able to repeat this process on other components/tables you don't have listed. Thanks.

Name

Email

Website

Title

Comment

Write the displayed characters

Add Comment