Improving Joomla's Queries

最新推荐文章于 2015-02-06 00:17:36 发布

chenjie3392593

最新推荐文章于 2015-02-06 00:17:36 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： jooma 文章标签： table access menu components templates user

本文链接：https://blog.youkuaiyun.com/chenjie3392593/article/details/7928653

jooma 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一套通过为Joomla核心及第三方组件如CommunityBuilder、Docman等添加索引来提高查询效率的方法，使得网站响应速度平均提升约0.1秒。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Improving Joomla's Queries

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 these 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));

refenrece:http://www.joomlaperformance.com/articles/performance/improving_joomlas_queries_4_14.html