Databases
StackOverflow2013
Postsdbo
POHow I can optimize this slow query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow I can optimize this slow query?
    primarykey
    data
    text
    <p>I want to know how I can optimize it, preferably without changes in tables structure.</p> <pre><code>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 </code></pre> <p>Output of <code>EXPLAIN</code> is:</p> <pre><code>------------------------------------------------------------------------------- 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) </code></pre> <p>Table structure is:</p> <pre><code>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&amp;-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&amp;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) </code></pre> <p>Table properties are:</p> <pre><code>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) </code></pre> <p>Other configuration details:</p> <pre><code>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) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDominic Rodger
    1. USPaulo Freitas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow I can optimize this slow query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow many rows is the output (roughly)? The explain tells us it is looking at what seems to be too many rows in the `forum_topics`-table, but I cannot be sure unless I know how many rows are expected...
      singulars
      1. POHow I can optimize this slow query?
      1. USVegard Larsen
    2. CO@Vegard Larsen: Good question! :) The output is about 4,006 rows. The query tooks from 2 to 5 seconds here, depending on the tables fragmentation. :|
      singulars
      1. POHow I can optimize this slow query?
      1. USPaulo Freitas
    3. COBuy a solid state drive? ;)
      singulars
      1. POHow I can optimize this slow query?
      1. USKurt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload