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