MySQL query optimization with expensive INNER JOIN

Question

Using the trial version and the error, I found that when you remove the connection from the request below, it works about 30 times faster. Can someone explain why this would be, and if it were possible to optimize the query to include an additional connection without a performance hit.

This is a screenshot of the explanation, which shows that the index is not used for the uesr_groups table.

http://i.imgur.com/9VDuV.png

This is the original request:

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 

If I remove the union "user_groups", the request will run 30 times faster than above.

 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 

My tables are below, can anyone give an idea on how to avoid a performance hit to include the user_groups table?

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

Answer 1

Most database engines calculate their query plan based on statistics about tables — for example, if a table has a small number of rows, it goes to the table faster than the index. These statistics are stored during a “normal” operation — for example, inserts, updates, and deletes — but may go out of sync when changing table definitions or when performing bulk inserts.

If you see unexpected behavior in terms of the query, you can force the database to update its statistics; in MySQL, you can use the Optimize Table - which does everything, including reordering the table itself or Analyzing a table that updates only indexes.

This is difficult to do in production environments, since both operations lock tables; if you can arrange a maintenance window, this is by far the easiest way to deal with this problem.

It is worth measuring the performance of "optimizing the table" - on well-defined equipment, for "normal" size tables it will take only a few seconds (up to the minimum million records, with only a few indexes). This may mean that you can have an “informal” service window - you do not take the application offline, you simply agree that some users will have poor performance when running scripts.

Answer 2

MySQL has an EXPLAIN function that helps you understand the query:

 $ mysql > EXPLAIN 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 

MySQL may simply be missing or missing an index.

You can learn more about understanding EXPLAIN output here from the documentation (a bit hard kernel) or, even better, from a simpler explanation here (ignore the fact that it is on a Java site.)

Most likely, the amount of data or an outdated or incomplete index means that MySQL scans the table erroneously. When you see a scan of a table or sequential servlets , you can often easily see which field is missing an index or an index that cannot be used.

Answer 3

Could you try this (you can remove the connection using user_group ). This can be faster if the query retrieves small data from the comments table:

 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 (select * from comments where comment_content_id = 12 and active = 1) comments INNER JOIN users u ON c.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 ORDER BY comments.comment_time_added ASC LIMIT 20 

Answer 4

Try using left joins for non-zero relationships.

It appears that since the inner joins are always symmetrical, mysql will reorder the joins to use the best (usually smallest) table first.

Since left joins are not always symmetrical, mysql will not modify them, and so you can use them to force table ordering. However, with a nonzero field on the left and inside are equivalent, so your results will not change.

The order of the tables will determine which features are used, which can greatly affect performance.