title: Optimizing MySQL query with expensive INNER JOIN
body: <p>Using trial and error i've discovered that when removing a join from the below query it runs around 30 times quicker. Can someone explain why this would be and if it's possible to optimise the query to include the additional join without the performance hit.</p>
  
  <p>This is a screenshot of the explain which shows that the index isn't being used for the uesr_groups table.</p>
  
  <p><img src="https://i.stack.imgur.com/A9NH9.png" alt="enter image description here"></p>
  
  <p><a href="https://i.imgur.com/9VDuV.png" rel="noreferrer">http://i.imgur.com/9VDuV.png</a></p>
  
  <p>This is the original query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If I remove the "user_groups" join then the query runs 30 times quicker as mentioned above.</p>
  
  <pre><code>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`
  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
  </code></pre>
  
  <p>My tables are below, can anyone offer any insight into how to avoid a performance hit for including the user_groups table?</p>
  
  <pre><code>--
  -- 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;
  </code></pre>