A forum on my site has over 300,000 threads which is resulting in up to 3-4 seconds of DB processing time.
The main debug query with biggest query time:
#30 - Select Query
SELECT t.*, tfd.`s_tags` AS `xthreads_s_tags`, tfd.`yt_auto` AS `xthreads_yt_auto`, tfd.`category` AS `xthreads_category`, tfd.`ytvgalid` AS `xthreads_ytvgalid`, tfd.`yt_direct` AS `xthreads_yt_direct`, tfd.`featurecheck` AS `xthreads_featurecheck`, t.username AS threadusername, u.username FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) LEFT JOIN `mybb_threadfields_data` tfd ON t.tid=tfd.tid WHERE t.fid='67' AND (t.visible IN (1,-1,0)) ORDER BY t.sticky DESC, t.lastpost desc LIMIT 0, 12
Table Type Possible Keys Key Key Length Ref Rows Extra
t ref fid fid 2 const 233830 Using index condition; Using where; Using filesort
u eq_ref PRIMARY PRIMARY 4 sharree.t.uid 1
tfd eq_ref PRIMARY PRIMARY 4 sharree.t.tid 1
Query Time: 1.615 seconds
This seems to be caused by the "// Start Getting Threads" part in the forumdisplay.php file
$query = $db->query("
SELECT t.*, {$ratingadd}t.username AS threadusername, u.username
FROM ".TABLE_PREFIX."threads t
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid = t.uid)
WHERE t.fid='$fid' $tuseronly $tvisibleonly $datecutsql2 $prefixsql2
ORDER BY t.sticky DESC, {$t}{$sortfield} $sortordernow $sortfield2
LIMIT $start, $perpage
When removing
t.sticky DESC, it completely fixes the problem but it disorganizes sticky threads. So is there an alternative to the // Start Getting Threads query?
Out of interest, run this query:
ALTER TABLE `mybb_threads` ADD INDEX `sticky` (`sticky`);
Does that help the performance at all?
[Major parts of this post struck out because I was confused due to unfamiliarity with the XThreads plugin. See
my later post in this thread which acknowledges that. At least the bits that I got right were helpful enough that Sharree accepted it as the solution.]
(2021-02-24, 09:33 PM)Sharree Wrote: [ -> ]This seems to be caused by the "// Start Getting Threads" part in the forumdisplay.php file
Based on the query you shared, I don't think you've identified the right part of the code. The query in the code is different. The query which is taking so long to run looks like it might be from the XThreads plugin (about which I know very little), since it references as xthreads_s_tags the s_tags column of a table which isn't present in my installation and which seems to also be from XThreads, mybb_threadfields_data. The puzzling thing for me though is that even after installing XThreads from its GitHub repository ([ETA: oh, and now I see here the warning "Note: do NOT use the Github version, unless you know what you're doing"... which I don't]), the query which is taking so long to run on your forum errored out on me with ERROR 1054 (42S22): Unknown column 'tfd.s_tags' in 'field list' - so there must be more to it than merely installing XThreads itself.
Given all of that, this is puzzling:
(2021-02-24, 09:33 PM)Sharree Wrote: [ -> ]When removing t.sticky DESC, it completely fixes the problem but it disorganizes sticky threads.
Perhaps both queries are taking an excessively long time to execute?
(2021-02-24, 10:11 PM)Matt Wrote: [ -> ]Out of interest, run this query:
ALTER TABLE `mybb_threads` ADD INDEX `sticky` (`sticky`);
Does that help the performance at all?
That's a good idea, although I don't think it includes enough columns to work for these queries.
Another possibility which might help for the runtime of the
first query is to add a slightly extended version of the existing
fid key:
ALTER TABLE `mybb_threads` ADD INDEX `fid2` (`fid`, `lastpost`, `visible`,`sticky`);
And this second extended version of the
fid key might help for the runtime of the
second query:
ALTER TABLE `mybb_threads` ADD INDEX `fid3` (`fid`, `sticky`, `lastpost`);
Hi, please try the attached Hooks file. My bigger sub forum has 6k+ thread, so it might not be as big as yours. But I saw a reduction of time from 3 ms to around 1200+ μs (microseconds). That would be less than half the query processing time for my board.
PHP processing time might increase, this is merely an experimental approach.
(2021-02-24, 10:11 PM)Matt Wrote: [ -> ]Out of interest, run this query:
ALTER TABLE `mybb_threads` ADD INDEX `sticky` (`sticky`);
Does that help the performance at all?
As far as I'm concerned indexes only help if the values vary from row to row, since
sticky can only be either
1 or
0 it won't help that much (if at all).
Plus, the default
fid key already has
fid,visible,sticky.
I think the issue is the ORDER BY clause ordering by
sticky, putting a big load on the
threads table, which in this case seems to store thousands of rows.
Edit: Also, I don't think this is caused by xThreads
@Sharree, how many rows are there in your threads table ? If you are willing to share that detail.
If any expert is willing to enlighten us with extra approaches .. 🧐
Interesting, Omar. You introduced me to a lot of new concepts/methods with that XML file (which I don't even know how to install - presumably it's part of a plugin like XThreads?). The most exciting was the
control_object function, which is a really neat find. Thanks for sharing.
(2021-02-24, 11:40 PM)Omar G. Wrote: [ -> ]I think the issue is the ORDER BY clause ordering by sticky, putting a big load on the threads table, which in this case seems to store thousands of rows.
I suspect it's also got to do with the ordering (by default) by
lastpost, given that no keys that are installed by default and are used in the query reference that column (judging by the
explain of the query).
[ETA: But your separation out of the query for sticky posts probably changes that, since then (I expect) the default key
lastpost would be used]
(2021-02-24, 11:40 PM)Omar G. Wrote: [ -> ]Hi, please try the attached Hooks file. My bigger sub forum has 6k+ thread, so it might not be as big as yours. But I saw a reduction of time from 3 ms to around 1200+ μs (microseconds). That would be less than half the query processing time for my board.
PHP processing time might increase, this is merely an experimental approach.
(2021-02-24, 10:11 PM)Matt Wrote: [ -> ]Out of interest, run this query:
ALTER TABLE `mybb_threads` ADD INDEX `sticky` (`sticky`);
Does that help the performance at all?
As far as I'm concerned indexes only help if the values vary from row to row, since sticky can only be either 1 or 0 it won't help that much (if at all).
Plus, the default fid key already has fid,visible,sticky.
I think the issue is the ORDER BY clause ordering by sticky, putting a big load on the threads table, which in this case seems to store thousands of rows.
Edit: Also, I don't think this is caused by xThreads
@Sharree, how many rows are there in your threads table ? If you are willing to share that detail.
If any expert is willing to enlighten us with extra approaches .. 🧐
Hey Omar sorry but how would I apply that XML file? Thanks
I have about 450,000 total rows in _threads
And yes I don't believe XThreads is the issue because I've tried disabling XThreads and it didn't have any affect on processing. Something else mentioned is s_tags, that's an XThreads custom field probably not related to the processing time either.
(2021-02-24, 10:11 PM)Matt Wrote: [ -> ]Out of interest, run this query:
ALTER TABLE `mybb_threads` ADD INDEX `sticky` (`sticky`);
Does that help the performance at all?
Actually I did try adding an index to Sticky last night unfortunately it didn't do anything for performance.
(2021-02-25, 06:25 AM)Omar G. Wrote: [ -> ]Oh, use the Hooks plugin to import the XML file. Very useful plugin for avoiding meaningless full plugins:
https://github.com/frostschutz/MyBB-Hooks
Hey Omar so it did work but I had to disable XThreads because the .xml file isn't considering XThreads custom fields. Here is the error I got back
https://i.gyazo.com/a328169c987ab6e59233...68bf2c.png
MyBB has experienced an internal SQL error and cannot continue.
SQL Error:
1054 - Unknown column 'tfd.s_tags' in 'field list'
Query:
SELECT t.*, tfd.`s_tags` AS `xthreads_s_tags`, tfd.`yt_auto` AS `xthreads_yt_auto`, tfd.`category` AS `xthreads_category`, tfd.`ytvgalid` AS `xthreads_ytvgalid`, tfd.`yt_direct` AS `xthreads_yt_direct`, tfd.`featurecheck` AS `xthreads_featurecheck`, t.username AS threadusername, u.username FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) WHERE t.sticky=1 AND t.fid='67' AND (t.visible IN (1)) ORDER BY IF(FIELD(t.tid, 402631,489964,490400,490039,488325,487411,489005,489852,490176,490223,490277,489802)=0,1,0),FIELD(t.tid, 402631,489964,490400,490039,488325,487411,489005,489852,490176,490223,490277,489802), t.lastpost desc LIMIT 0, 12
With that hook the processing time dropped from 3 seconds to 550ms. Anyway to work in XThreads custom fields into the code?
Try the attached Hook instead. It is possible that you find additional issues with other plugins that you might need to fix on the way. I have, for example, some plugins which add data to the thread cache in a similar way to how xThreads does.
The previous attachment might work just fine in vanilla install, so I won't remove it.
(2021-02-25, 12:09 AM)Laird Wrote: [ -> ]The most exciting was the control_object function
I bet you already have some crazy stuff to implement it on, huh? xThreads made crazy stuff to my plugin ideas
🤪