How I can optimize this slow query?

Question

I want to know how I can optimize it, preferably without changes in tables structure.

SELECT p.author_id member_id,
       m.members_display_name member_name,
       COUNT(p.pid) posts
FROM forum_topics t
     STRAIGHT_JOIN forum_posts p
     STRAIGHT_JOIN forum_members m
WHERE p.author_id != 0
      AND p.author_id = m.member_id
      AND p.new_topic = 0
      AND t.forum_id = 120
      AND t.tid = p.topic_id
GROUP BY p.author_id
ORDER BY posts DESC
LIMIT 1

Output of EXPLAIN is:

-------------------------------------------------------------------------------
 EXPLAIN
  SELECT p.author_id member_id,
         m.members_display_name member_name,
         COUNT(p.pid) posts
    FROM forum_topics t
         STRAIGHT_JOIN forum_posts p
         STRAIGHT_JOIN forum_members m
   WHERE p.author_id != 0
         AND p.author_id = m.member_id
         AND p.new_topic = 0
         AND t.forum_id = 120
         AND t.tid = p.topic_id
GROUP BY p.author_id
ORDER BY posts DESC
   LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: PRIMARY,forum_id,last_post
          key: forum_id
      key_len: 2
          ref: const
         rows: 28070
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: author_id,topic_id
          key: topic_id
      key_len: 4
          ref: forumwmo.t.tid
         rows: 5
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: forumwmo.p.author_id
         rows: 1
        Extra: 
3 rows in set (0.00 sec)

Table structure is:

SHOW CREATE TABLE forum_members\G
*************************** 1. row ***************************
       Table: forum_members
Create Table: CREATE TABLE `forum_members` (
  `member_id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `member_group_id` smallint(3) NOT NULL DEFAULT '0',
  `email` varchar(150) NOT NULL DEFAULT '',
  `joined` int(10) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '',
  `posts` mediumint(7) DEFAULT '0',
  `title` varchar(64) DEFAULT NULL,
  `allow_admin_mails` tinyint(1) DEFAULT NULL,
  `time_offset` varchar(10) DEFAULT NULL,
  `hide_email` varchar(8) DEFAULT NULL,
  `email_pm` tinyint(1) DEFAULT NULL,
  `email_full` tinyint(1) DEFAULT NULL,
  `skin` smallint(5) DEFAULT NULL,
  `warn_level` int(10) DEFAULT NULL,
  `warn_lastwarn` int(10) NOT NULL DEFAULT '0',
  `language` varchar(32) DEFAULT NULL,
  `last_post` int(10) DEFAULT NULL,
  `restrict_post` varchar(100) NOT NULL DEFAULT '0',
  `view_sigs` tinyint(1) DEFAULT '1',
  `view_img` tinyint(1) DEFAULT '1',
  `view_avs` tinyint(1) DEFAULT '1',
  `view_pop` tinyint(1) DEFAULT '1',
  `bday_day` int(2) DEFAULT NULL,
  `bday_month` int(2) DEFAULT NULL,
  `bday_year` int(4) DEFAULT NULL,
  `msg_count_new` int(2) NOT NULL DEFAULT '0',
  `msg_count_total` int(3) NOT NULL DEFAULT '0',
  `msg_count_reset` int(1) NOT NULL DEFAULT '0',
  `msg_show_notification` int(1) NOT NULL DEFAULT '0',
  `misc` varchar(128) DEFAULT NULL,
  `last_visit` int(10) DEFAULT '0',
  `last_activity` int(10) DEFAULT '0',
  `dst_in_use` tinyint(1) DEFAULT '0',
  `view_prefs` varchar(64) DEFAULT '-1&-1',
  `coppa_user` tinyint(1) DEFAULT '0',
  `mod_posts` varchar(100) NOT NULL DEFAULT '0',
  `auto_track` varchar(50) DEFAULT '0',
  `org_perm_id` varchar(255) DEFAULT '',
  `temp_ban` varchar(100) DEFAULT '0',
  `sub_end` int(10) NOT NULL DEFAULT '0',
  `no_sig_lims` tinyint(1) NOT NULL DEFAULT '0',
  `login_anonymous` char(3) NOT NULL DEFAULT '0&0',
  `ignored_users` text,
  `mgroup_others` varchar(255) NOT NULL DEFAULT '',
  `member_login_key` varchar(32) NOT NULL DEFAULT '',
  `member_login_key_expire` int(10) NOT NULL DEFAULT '0',
  `subs_pkg_chosen` smallint(3) NOT NULL DEFAULT '0',
  `has_blog` tinyint(1) NOT NULL DEFAULT '0',
  `members_auto_dst` tinyint(1) NOT NULL DEFAULT '1',
  `members_cache` mediumtext,
  `members_disable_pm` int(1) NOT NULL DEFAULT '0',
  `members_display_name` varchar(255) NOT NULL DEFAULT '',
  `members_seo_name` varchar(255) NOT NULL DEFAULT '',
  `members_created_remote` tinyint(1) NOT NULL DEFAULT '0',
  `members_editor_choice` char(3) NOT NULL DEFAULT 'std',
  `members_profile_views` int(10) unsigned NOT NULL DEFAULT '0',
  `members_l_display_name` varchar(255) NOT NULL DEFAULT '0',
  `members_l_username` varchar(255) NOT NULL DEFAULT '0',
  `failed_logins` text,
  `failed_login_count` smallint(3) NOT NULL DEFAULT '0',
  `has_gallery` int(1) DEFAULT '0',
  `members_pass_hash` varchar(32) NOT NULL DEFAULT '',
  `members_pass_salt` varchar(5) NOT NULL DEFAULT '',
  `member_banned` tinyint(1) NOT NULL DEFAULT '0',
  `identity_url` text,
  `member_uploader` varchar(32) NOT NULL DEFAULT 'default',
  `members_bitoptions` int(10) unsigned NOT NULL DEFAULT '0',
  `fb_uid` bigint(20) NOT NULL DEFAULT '0',
  `fb_emailhash` varchar(60) NOT NULL DEFAULT '',
  `fb_emailallow` int(1) NOT NULL DEFAULT '0',
  `fb_lastsync` int(10) NOT NULL DEFAULT '0',
  `members_day_posts` varchar(32) NOT NULL DEFAULT '0,0',
  `live_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  KEY `mgroup` (`member_group_id`),
  KEY `bday_day` (`bday_day`),
  KEY `bday_month` (`bday_month`),
  KEY `members_l_display_name` (`members_l_display_name`),
  KEY `members_l_username` (`members_l_username`),
  KEY `member_banned` (`member_banned`),
  KEY `members_bitoptions` (`members_bitoptions`),
  KEY `ip_address` (`ip_address`)
) ENGINE=MyISAM AUTO_INCREMENT=101965 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW CREATE TABLE forum_posts\G
*************************** 1. row ***************************
       Table: forum_posts
Create Table: CREATE TABLE `forum_posts` (
  `append_edit` tinyint(1) DEFAULT '0',
  `edit_time` int(10) DEFAULT NULL,
  `pid` int(10) NOT NULL AUTO_INCREMENT,
  `author_id` mediumint(8) NOT NULL DEFAULT '0',
  `author_name` varchar(32) DEFAULT NULL,
  `use_sig` tinyint(1) NOT NULL DEFAULT '0',
  `use_emo` tinyint(1) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '',
  `post_date` int(10) DEFAULT NULL,
  `icon_id` smallint(3) DEFAULT NULL,
  `post` mediumtext,
  `queued` tinyint(1) NOT NULL DEFAULT '0',
  `topic_id` int(10) NOT NULL DEFAULT '0',
  `post_title` varchar(255) DEFAULT NULL,
  `new_topic` tinyint(1) DEFAULT '0',
  `edit_name` varchar(255) DEFAULT NULL,
  `post_parent` int(10) NOT NULL DEFAULT '0',
  `post_key` varchar(32) NOT NULL DEFAULT '0',
  `post_htmlstate` smallint(1) NOT NULL DEFAULT '0',
  `post_edit_reason` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`pid`),
  KEY `author_id` (`author_id`,`topic_id`),
  KEY `post_date` (`post_date`),
  KEY `topic_id` (`topic_id`,`queued`,`pid`,`post_date`),
  KEY `post_key` (`post_key`),
  KEY `ip_address` (`ip_address`)
) ENGINE=MyISAM AUTO_INCREMENT=988489 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW CREATE TABLE forum_topics\G
*************************** 1. row ***************************
       Table: forum_topics
Create Table: CREATE TABLE `forum_topics` (
  `tid` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) NOT NULL DEFAULT '',
  `description` varchar(250) DEFAULT NULL,
  `state` varchar(8) DEFAULT NULL,
  `posts` int(10) DEFAULT NULL,
  `starter_id` mediumint(8) NOT NULL DEFAULT '0',
  `start_date` int(10) DEFAULT NULL,
  `last_poster_id` mediumint(8) NOT NULL DEFAULT '0',
  `last_post` int(10) DEFAULT NULL,
  `icon_id` tinyint(2) DEFAULT NULL,
  `starter_name` varchar(255) DEFAULT NULL,
  `last_poster_name` varchar(255) DEFAULT NULL,
  `poll_state` varchar(8) DEFAULT NULL,
  `last_vote` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `forum_id` smallint(5) NOT NULL DEFAULT '0',
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  `author_mode` tinyint(1) DEFAULT NULL,
  `pinned` tinyint(1) NOT NULL DEFAULT '0',
  `moved_to` varchar(64) DEFAULT NULL,
  `total_votes` int(5) NOT NULL DEFAULT '0',
  `topic_hasattach` smallint(5) NOT NULL DEFAULT '0',
  `topic_firstpost` int(10) NOT NULL DEFAULT '0',
  `topic_queuedposts` int(10) NOT NULL DEFAULT '0',
  `topic_rating_total` smallint(5) unsigned NOT NULL DEFAULT '0',
  `topic_rating_hits` smallint(5) unsigned NOT NULL DEFAULT '0',
  `topic_open_time` int(10) NOT NULL DEFAULT '0',
  `topic_close_time` int(10) NOT NULL DEFAULT '0',
  `title_seo` varchar(250) NOT NULL DEFAULT '',
  `seo_last_name` varchar(255) NOT NULL DEFAULT '',
  `seo_first_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`tid`),
  KEY `topic_firstpost` (`topic_firstpost`),
  KEY `forum_id` (`forum_id`,`pinned`,`approved`),
  KEY `last_post` (`forum_id`,`pinned`,`last_post`),
  KEY `starter_id` (`starter_id`,`forum_id`,`approved`),
  KEY `last_post_sorting` (`last_post`,`forum_id`),
  KEY `start_date` (`start_date`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=227233 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Table properties are:

SHOW TABLE STATUS LIKE 'forum_members'\G
*************************** 1. row ***************************
           Name: forum_members
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 64866
 Avg_row_length: 257
    Data_length: 16688164
Max_data_length: 281474976710655
   Index_length: 6540288
      Data_free: 0
 Auto_increment: 101965
    Create_time: 2009-12-02 05:45:59
    Update_time: 2009-12-02 05:46:07
     Check_time: 2009-12-02 05:49:23
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW TABLE STATUS LIKE 'forum_posts'\G
*************************** 1. row ***************************
           Name: forum_posts
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 581777
 Avg_row_length: 720
    Data_length: 419170168
Max_data_length: 281474976710655
   Index_length: 46439424
      Data_free: 0
 Auto_increment: 988489
    Create_time: 2009-12-02 05:47:04
    Update_time: 2009-12-02 05:48:13
     Check_time: 2009-12-02 05:49:28
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-------------------------------------------------------------------------------
SHOW TABLE STATUS LIKE 'forum_topics'\G
*************************** 1. row ***************************
           Name: forum_topics
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 117458
 Avg_row_length: 144
    Data_length: 17004836
Max_data_length: 281474976710655
   Index_length: 14105600
      Data_free: 0
 Auto_increment: 227233
    Create_time: 2009-12-02 05:48:38
    Update_time: 2009-12-02 05:48:43
     Check_time: 2009-12-02 05:49:28
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Other configuration details:

SHOW VARIABLES LIKE '%buffer%'\G
*************************** 1. row ***************************
Variable_name: bulk_insert_buffer_size
        Value: 8388608
*************************** 2. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 8388608
*************************** 3. row ***************************
Variable_name: innodb_log_buffer_size
        Value: 1048576
*************************** 4. row ***************************
Variable_name: join_buffer_size
        Value: 131072
*************************** 5. row ***************************
Variable_name: key_buffer_size
        Value: 8384512
*************************** 6. row ***************************
Variable_name: myisam_sort_buffer_size
        Value: 8388608
*************************** 7. row ***************************
Variable_name: net_buffer_length
        Value: 16384
*************************** 8. row ***************************
Variable_name: pbxt_log_buffer_size
        Value: 256K
*************************** 9. row ***************************
Variable_name: pbxt_transaction_buffer_size
        Value: 1MB
*************************** 10. row ***************************
Variable_name: preload_buffer_size
        Value: 32768
*************************** 11. row ***************************
Variable_name: read_buffer_size
        Value: 131072
*************************** 12. row ***************************
Variable_name: read_rnd_buffer_size
        Value: 262144
*************************** 13. row ***************************
Variable_name: sort_buffer_size
        Value: 2097144
*************************** 14. row ***************************
Variable_name: sql_buffer_result
        Value: OFF
14 rows in set (0.00 sec)

Answer 1

My guess would be that you are missing an index on p.new_topic. Thus it has to go across all the posts.

Then, I wouldn't use explicit joins. Just let optimizer do his work without puting extra constraints on him.

Last, I don't know how indices work if you set them on multiples columns (what you do a lot). Maybe looking some information about that might help.

Answer 2

You might gain increased performance by moving some of your WHERE clause conditions up into your JOINs since this will cut down the result set as the query is executed e.g.

SELECT p.author_id            member_id,
       m.members_display_name member_name,
       COUNT(p.pid)           posts
FROM forum_topics t
     STRAIGHT_JOIN forum_posts p 
     ON            t.tid      = p.topic_id 
     AND           t.forum_id = 120 
     AND p.new_topic = 0
     STRAIGHT_JOIN forum_members m 
     ON p.author_id = m.member_id
WHERE p.author_id   != 0      
GROUP BY p.author_id
ORDER BY posts DESC
LIMIT 1

Although, the optimizer may be smart enough to do this for you. Also, although I don't use MySQL, my understanding of the STRAIGH_JOIN is that it joins that tables in an explicit left to right order so it may make a difference if the tables are listed in size order from smallest to largest and vica versa.

Answer 3

Buy a solid state drive? ;)