使用试验和错误我发现,从以下查询中删除连接时,它运行速度提高了大约30倍.有人可以解释为什么会这样,并且是否可以优化查询以包含额外的连接而不会影响性能.
这是解释的屏幕截图,显示该索引未用于uesr_groups表.
这是原始查询：
SELECT `comments`.`comment_id`,`comments`.`comment_html`,`comments`.`comment_time_added`,`comments`.`comment_has_attachments`,`users`.`user_name`,`users`.`user_id`,`users`.`user_comments_count`,`users`.`user_time_registered`,`users`.`user_time_last_active`,`user_profile`.`user_avatar`,`user_profile`.`user_signature_html`,`user_groups`.`user_group_icon`,`user_groups`.`user_group_name`
FROM (`comments`)
INNER JOIN `users` ON `comments`.`comment_user_id` = `users`.`user_id`
INNER JOIN `user_profile` ON `users`.`user_id` = `user_profile`.`user_id`
INNER JOIN `user_groups` ON `users`.`user_group_id` = `user_groups`.`user_group_id`
WHERE `comments`.`comment_enabled` = 1
AND `comments`.`comment_content_id` = 12
ORDER BY `comments`.`comment_time_added` ASC
LIMIT 20
如果我删除“user_groups”连接,则查询运行速度提高30倍,如上所述.
SELECT `comments`.`comment_id`,`user_profile`.`user_signature_html`
FROM (`comments`)
INNER JOIN `users` ON `comments`.`comment_user_id` = `users`.`user_id`
INNER JOIN `user_profile` ON `users`.`user_id` = `user_profile`.`user_id`
WHERE `comments`.`comment_enabled` = 1
AND `comments`.`comment_content_id` = 12
ORDER BY `comments`.`comment_time_added` ASC
LIMIT 20
我的表格如下,任何人都可以提供任何有关如何避免包含user_groups表格的性能影响的见解吗？
--
-- Table structure for table `comments`
--
CREATE TABLE IF NOT EXISTS `comments` (
`comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`comment_content_id` int(10) unsigned NOT NULL,`comment_user_id` mediumint(6) unsigned NOT NULL,`comment_original` text NOT NULL,`comment_html` text NOT NULL,`comment_time_added` int(10) unsigned NOT NULL,`comment_time_updated` int(10) unsigned NOT NULL,`comment_enabled` tinyint(1) NOT NULL DEFAULT '0',`comment_is_spam` tinyint(1) NOT NULL DEFAULT '0',`comment_has_attachments` tinyint(1) unsigned NOT NULL,`comment_has_edits` tinyint(1) NOT NULL,PRIMARY KEY (`comment_id`),KEY `comment_user_id` (`comment_user_id`),KEY `comment_content_id` (`comment_content_id`),KEY `comment_is_spam` (`comment_is_spam`),KEY `comment_enabled` (`comment_enabled`),KEY `comment_time_updated` (`comment_time_updated`),KEY `comment_time_added` (`comment_time_added`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=352 ;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`user_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,`user_ipb_id` int(10) unsigned DEFAULT NULL,`user_activated` tinyint(1) NOT NULL DEFAULT '0',`user_name` varchar(64) CHARACTER SET latin1 NOT NULL,`user_email` varchar(255) NOT NULL,`user_password` varchar(40) NOT NULL,`user_content_count` int(10) unsigned NOT NULL DEFAULT '0',`user_comments_count` int(10) unsigned NOT NULL DEFAULT '0',`user_salt` varchar(8) NOT NULL,`user_api_key` varchar(32) NOT NULL,`user_auth_key` varchar(32) DEFAULT NULL,`user_paypal_key` varchar(32) DEFAULT NULL,`user_timezone_id` smallint(3) unsigned NOT NULL,`user_group_id` tinyint(3) unsigned NOT NULL,`user_custom_permission_mask_id` tinyint(3) unsigned DEFAULT NULL,`user_lang_id` tinyint(2) unsigned NOT NULL,`user_time_registered` int(10) unsigned NOT NULL,`user_time_last_active` int(10) unsigned NOT NULL
PRIMARY KEY (`user_id`),UNIQUE KEY `user_email` (`user_email`),KEY `user_group_id` (`user_group_id`),KEY `user_auth_key` (`user_auth_key`),KEY `user_api_key` (`user_api_key`),KEY `user_custom_permission_mask_id` (`user_custom_permission_mask_id`),KEY `user_time_last_active` (`user_time_last_active`),KEY `user_paypal_key` (`user_paypal_key`),KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;
-- --------------------------------------------------------
--
-- Table structure for table `user_groups`
--
CREATE TABLE IF NOT EXISTS `user_groups` (
`user_group_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,`user_group_name` varchar(32) NOT NULL,`user_group_permission_mask_id` tinyint(3) unsigned NOT NULL,`user_group_icon` varchar(32) DEFAULT NULL,PRIMARY KEY (`user_group_id`),KEY `user_group_permission_mask_id` (`user_group_permission_mask_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
-- --------------------------------------------------------
--
-- Table structure for table `user_profile`
--
CREATE TABLE IF NOT EXISTS `user_profile` (
`user_id` mediumint(8) unsigned NOT NULL,`user_signature_original` text,`user_signature_html` text,`user_avatar` varchar(64) DEFAULT NULL,`user_steam_id` varchar(64) DEFAULT NULL,`user_ps_id` varchar(16) DEFAULT NULL,`user_xBox_id` varchar(64) DEFAULT NULL,`user_wii_id` varchar(64) DEFAULT NULL,PRIMARY KEY (`user_id`),KEY `user_steam_id` (`user_steam_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
