mysql inner join 优化,使用昂贵的INNER JOIN优化MySQL查询

使用试验和错误我发现,从以下查询中删除连接时,它运行速度提高了大约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;