View Issue Details

IDProjectCategoryView StatusLast Update
0000416XMB1Bugspublic2011-02-11 09:45
Reportermiqrogroove Assigned Tomiqrogroove  
Status closedResolutionfixed 
Product Version1.9.8 SP2 
Target Version1.9.11.11Fixed in Version1.9.11.11 
Summary0000416: Threads Table Optimization
DescriptionThe ORDER BY clause used in the forumdisplay script causes MySQL to use its filesort strategy to execute the query. I was able to fix this in real time by adding a multi-column index to the threads table:

ALTER TABLE `xmb_threads` ADD INDEX `forum_optimize` ( `fid` , `topped` , `lastpost` )

I then confirmed MySQL was using the forum_optimize index and not using filesort for that particular case.
Steps To ReproduceRun a query with an appopriate fid number such as

EXPLAIN SELECT t.*, m.uid, r.uid AS lastauthor FROM xmb_threads AS t LEFT JOIN xmb_members AS m ON = m.username LEFT JOIN xmb_members AS r ON SUBSTRING_INDEX(SUBSTRING_INDEX(t.lastpost, '|', 2), '|', -1) = r.username WHERE t.fid='11' ORDER BY topped desc, lastpost desc LIMIT 0, 65
TagsNo tags attached.
MySQL Version5.0
PHP Version
Web Server
FlagsSchema Updates
Original Reporter
SVN Revision2435


related to 0000421 new LIMIT start, ppp Does Not Scale Well 
related to 0000422 new Run Thread Query in Reverse? 



2011-01-14 02:25

administrator   ~0000278

A similar change is needed in the posts table:

ALTER TABLE `xmb_posts` ADD INDEX `thread_optimize` ( `tid` , `dateline` , `pid` )

It will have only a minor impact, except on very large threads. When benchmarked on the Exhale server's largest thread, the primary query for viewthread.php ran 50 times faster with this new index.


2011-01-14 12:05

administrator   ~0000279

Note also the viewthread query is not optimized to use that index to its full potential on large page numbers. The LIMIT statement makes the query run slowly at the end of the thread. To be totally optimized, we would have to think of a way to add something like WHERE dateline > $some_number.

Coincidentally, the dateline criteria are already being queried for use on the logs table union. I can employ that same information to speed up the posts table. :)


2011-01-14 12:21

administrator   ~0000280

The query that gathers the dateline criteria is already running 10 times faster than before thanks to the new index. This is going to work out very well.


2011-01-14 15:22

administrator   ~0000281

Schema changes have been committed. Do not run upgrade.php if you are already at 1.9.11. All you will need to do is add the 2 new indexes and drop the 2 old ones. New ticket to follow regarding the upper limit of this optimization.


2011-01-14 15:37

administrator   ~0000282

From the Insert Raw SQL screen in XMB, one can use this code:

ALTER TABLE $table_threads DROP INDEX fid, ADD INDEX forum_optimize (fid, topped, lastpost);
ALTER TABLE $table_posts DROP INDEX tid, ADD INDEX thread_optimize (tid, dateline, pid);


2011-01-16 15:47

administrator   ~0000287

Needs more work. Some of the logic is inconsistent and not clearly coded.


2011-01-16 16:39

administrator   ~0000288

Working perfectly now.

Issue History

Date Modified Username Field Change
2011-01-11 08:13 miqrogroove New Issue
2011-01-11 08:13 miqrogroove Flags => Schema Updates
2011-01-11 08:15 miqrogroove Description Updated
2011-01-11 08:15 miqrogroove MySQL Version => 5.0
2011-01-11 15:58 miqrogroove Projection none => tweak
2011-01-11 15:58 miqrogroove Product Version => 1.9.8 SP2
2011-01-14 02:25 miqrogroove Note Added: 0000278
2011-01-14 12:05 miqrogroove Note Added: 0000279
2011-01-14 12:21 miqrogroove Note Added: 0000280
2011-01-14 15:22 miqrogroove SVN Revision => 2424
2011-01-14 15:22 miqrogroove Note Added: 0000281
2011-01-14 15:22 miqrogroove Status new => resolved
2011-01-14 15:22 miqrogroove Fixed in Version =>
2011-01-14 15:22 miqrogroove Resolution open => fixed
2011-01-14 15:22 miqrogroove Assigned To => miqrogroove
2011-01-14 15:37 miqrogroove Note Added: 0000282
2011-01-14 15:38 miqrogroove Target Version =>
2011-01-14 16:53 miqrogroove Relationship added related to 0000421
2011-01-15 20:06 miqrogroove Relationship added related to 0000422
2011-01-16 15:47 miqrogroove Note Added: 0000287
2011-01-16 15:47 miqrogroove Status resolved => assigned
2011-01-16 16:39 miqrogroove SVN Revision 2424 => 2435
2011-01-16 16:39 miqrogroove Status assigned => resolved
2011-01-16 16:39 miqrogroove Note Added: 0000288
2011-02-11 09:45 miqrogroove Status resolved => closed