| Improving Joomla's Queries |
| Written by ircmaxell | |
| Tuesday, 26 June 2007 | |
|
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(0)
Comments (11)
![]() 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.
written by Milos, July 06, 2007
This procedure dramaticly increased load on mine vps... So i needed to revert to backup.
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
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.
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! 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.
Write comment
|
|
| Last Updated ( Tuesday, 26 June 2007 ) |
| < Prev | Next > |
|---|












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