Quick update after testing a targeted fix:
There are three files that contain the code USE INDEX (indx_2) - libs/topic_tree.inc.php, scripts/showflat.inc.php, and scripts/domailthread.inc.php. I made backups of the files and then removed that code from all three files. Here are the results. I used
[root@ded602 mysqld]# pidstat -u -p $(pgrep mysqld) 5 > mysqld-cpu.log 2>&1 &
to monitor CPU usage.
Pre-change (from earlier logs):
Frequent extreme spikes: 300–800% CPU (e.g., 779.60%, 788.42%, 790.62%, 787.62%)
Sustained high usage: many entries 100–500%+
Caused by queries scanning ~1M rows each time due to forced wrong index
Post-change (latest tail):
CPU usage very low and stable: 0.2–4.6% (typical 0.4–1.8%)
No spikes at all in the sample
Normal background activity only
Difference explanation:
Removing USE INDEX (indx_2) lets MySQL use the PRIMARY KEY on POST_ID for direct row lookups instead of scanning ~1 million rows per query, eliminating the CPU-intensive table scans and dropping usage from massive spikes to near-idle levels.
It''s up to you, but I would highly recommend you implement this change in your code. I'd be happy to share exact diffs if helpful.
