Index to fix this query MySQL

Question

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the slow query log and we'd like to fix it but we're unsure of where to begin. The query is

SELECT post_thanks.postid AS postid
FROM post_thanks AS post_thanks
LEFT JOIN post AS post
 USING (postid)
INNER JOIN thread AS thread
 ON(thread.threadid = post.threadid)
WHERE post_thanks.userid = 125004
 AND thread.forumid IN(57,58,83,10,39,30,49,52,115,11,103,12,55,42,119,120,54,14,33,107,13,106,38,41,128,24,71,134,138,139,142,125,144,135,79,132,124,122,130,129,73,143,126,123,140,145,127,26,114,137,146,136,74,121,108,15,19,72,46,47,48,45,34,98,36,50,21,29,7,3,6,8,113,76,28,37,43,112,116,59,60,110,32,62,64,63,69,65,67,25,61,66,68,141,1,2,9,111,53)
ORDER BY post.dateline DESC
LIMIT 1000;

Tables are as follows

post_thanks

| post_thanks | CREATE TABLE `post_thanks` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `userid` int(10) NOT NULL DEFAULT '0',
 `username` varchar(50) NOT NULL DEFAULT '',
 `date` int(10) NOT NULL DEFAULT '0',
 `postid` int(10) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `postid` (`postid`)
) ENGINE=InnoDB AUTO_INCREMENT=7888982 DEFAULT CHARSET=latin1 |

post

| post | CREATE TABLE `post` (
 `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `threadid` int(10) unsigned NOT NULL DEFAULT '0',
 `parentid` int(10) unsigned NOT NULL DEFAULT '0',
 `username` varchar(100) NOT NULL DEFAULT '',
 `userid` int(10) unsigned NOT NULL DEFAULT '0',
 `title` varchar(250) NOT NULL DEFAULT '',
 `dateline` int(10) unsigned NOT NULL DEFAULT '0',
 `pagetext` mediumtext,
 `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
 `showsignature` smallint(6) NOT NULL DEFAULT '0',
 `ipaddress` varchar(15) NOT NULL DEFAULT '',
 `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
 `visible` smallint(6) NOT NULL DEFAULT '0',
 `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
 `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
 `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
 `ame_flag` tinyint(4) NOT NULL DEFAULT '0',
 `post_thanks_amount` int(10) unsigned NOT NULL DEFAULT '0',
 `dbtech_thanks_cache` mediumtext,
 `dbtech_thanks_disabledbuttons` int(10) unsigned NOT NULL DEFAULT '0',
 `dbtech_thanks_requiredbuttons_content` int(10) unsigned NOT NULL DEFAULT '0',
 `dbtech_thanks_requiredbuttons_attach` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`postid`),
 KEY `userid` (`userid`),
 KEY `threadid` (`threadid`,`userid`),
 KEY `dateline` (`dateline`),
 KEY `thread_date` (`threadid`,`dateline`),
 FULLTEXT KEY `title` (`title`,`pagetext`)
) ENGINE=MyISAM AUTO_INCREMENT=6446460 DEFAULT CHARSET=latin1 |

thread

| thread | CREATE TABLE `thread` (
 `threadid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(250) NOT NULL DEFAULT '',
 `firstpostid` int(10) unsigned NOT NULL DEFAULT '0',
 `lastpostid` int(10) unsigned NOT NULL DEFAULT '0',
 `lastpost` int(10) unsigned NOT NULL DEFAULT '0',
 `forumid` smallint(5) unsigned NOT NULL DEFAULT '0',
 `pollid` int(10) unsigned NOT NULL DEFAULT '0',
 `open` smallint(6) NOT NULL DEFAULT '0',
 `replycount` int(10) unsigned NOT NULL DEFAULT '0',
 `hiddencount` int(10) unsigned NOT NULL DEFAULT '0',
 `deletedcount` int(10) unsigned NOT NULL DEFAULT '0',
 `postusername` varchar(100) NOT NULL DEFAULT '',
 `postuserid` int(10) unsigned NOT NULL DEFAULT '0',
 `lastposter` varchar(100) NOT NULL DEFAULT '',
 `dateline` int(10) unsigned NOT NULL DEFAULT '0',
 `views` int(10) unsigned NOT NULL DEFAULT '0',
 `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
 `notes` varchar(250) NOT NULL DEFAULT '',
 `visible` smallint(6) NOT NULL DEFAULT '0',
 `sticky` smallint(6) NOT NULL DEFAULT '0',
 `votenum` smallint(5) unsigned NOT NULL DEFAULT '0',
 `votetotal` smallint(5) unsigned NOT NULL DEFAULT '0',
 `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
 `similar` varchar(55) NOT NULL DEFAULT '',
 `prefixid` varchar(25) NOT NULL DEFAULT '',
 `taglist` mediumtext,
 `dbtech_thanks_disabledbuttons` int(10) unsigned NOT NULL DEFAULT '0',
 `dbtech_thanks_requiredbuttons_content` int(10) unsigned NOT NULL DEFAULT '0',
 `dbtech_thanks_requiredbuttons_attach` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`threadid`),
 KEY `postuserid` (`postuserid`),
 KEY `pollid` (`pollid`),
 KEY `forumid` (`forumid`,`visible`,`sticky`,`lastpost`),
 KEY `lastpost` (`lastpost`,`forumid`),
 KEY `dateline` (`dateline`),
 KEY `prefixid` (`prefixid`,`forumid`),
 FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=345718 DEFAULT CHARSET=latin1 |

EXPLAIN

+------+-------------+-------------+--------+------------------------------+----------+---------+-------------------------------------------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------+--------+------------------------------+----------+---------+-------------------------------------------+------+----------+------------------------------------+
| 1 | SIMPLE | post | index | PRIMARY,threadid,thread_date | dateline | 4 | NULL | 1000 | 100.00 | |
| 1 | SIMPLE | thread | eq_ref | PRIMARY,forumid | PRIMARY | 4 | survivalistboards_vbulletin.post.threadid | 1 | 100.00 | Using where |
| 1 | SIMPLE | post_thanks | ref | postid | postid | 4 | survivalistboards_vbulletin.post.postid | 1 | 100.00 | Using index condition; Using where |
+------+-------------+-------------+--------+------------------------------+----------+---------+-------------------------------------------+------+----------+------------------------------------+

• Michael Green: Does putting the huge IN() list into a properly indexed table help?

Answer 1

Tips regarding the three tables and their indexes:

1. A new combined index (threadid, dateline) on post might help. The first part should help while joining, while the second part eases the sorting.

   Right now only the index (dateline) is used to avoid filesort, but the whole index has to be scanned (see the first row of explain, which says type=index). By the way: There are two identical indexes (dateline) on post right now. One can be deleted.

2. You could extend the index on post_thanks. Right now it only contains the column (postid), but your query restricts the result set by userid. So adding this column as the first part of the existing index should help too -> (userid, postid).

3. For the table thread the index over (threadid) is used. Since your query restricts the result set by specifying valid values for forumid, it might help to add a new index (threadid, forumid). Again the first part should help to join while the second part should help to find matching rows.

Attention: Actually the last two indexes might have to be reversed, if the first change proposal is applied! Right now the optimizer decided that it is faster to scan (the index of) post completely and then join in data from the other two tables. If there exists a proper index on post, it might instead be faster to first select only those rows of post_thanks and/or thread, which are relevant, and then join the post table in. Since the join is then applied after selecting rows, the index tips 2. and 3. should contain first the column which restricts the selection and second the column which helps joining.

If you have applied some (or all) changes, you might update your posting with the current output of EXPLAIN. Maybe it shows further space for improvement.

I would start by applying change 1. and take a look at EXPLAIN again to decide how to go on.

• user36445: Thank you for your help. We will take care of Step 1 this afternoon and report back.
• user36445: Added combined index. Didn't appear to help at all. Updating first post.
• Ulrich Thomas Gabor: Then I would add an index (userid, postid) on post_thanks. Since this is the table with the most rows (according to the auto_increment values) and this index helps in finding matching rows. Please add a new output of EXPLAIN below the existing one, so we are able to compare.
• oNare: Did you try adding 1 index on post_thanks.userid?
• ypercubeᵀᴹ: An index on (userid, postid) will be more efficient for this query than one on (postid, userid).