View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000416 | XMB1 | Bugs | public | 2011-01-11 08:13 | 2011-02-11 09:45 |
| Reporter | miqrogroove | Assigned To | miqrogroove | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 1.9.8 SP2 | ||||
| Target Version | 1.9.11.11 | Fixed in Version | 1.9.11.11 | ||
| Summary | 0000416: Threads Table Optimization | ||||
| Description | The 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 Reproduce | Run 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 t.author = 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 | ||||
| Tags | No tags attached. | ||||
| MySQL Version | 5.0 | ||||
| PHP Version | |||||
| Web Server | |||||
| Browser | |||||
| Flags | Schema Updates | ||||
| Original Reporter | |||||
| SVN Revision | 2435 | ||||
|
|
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. |
|
|
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. :) |
|
|
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. |
|
|
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. |
|
|
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); |
|
|
Needs more work. Some of the logic is inconsistent and not clearly coded. |
|
|
Working perfectly now. |
| 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 | => 1.9.11.11 |
| 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 | => 1.9.11.11 |
| 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 |
