Looking at my slow query log, the biggest offender is the search query. It's looking at every single row in the db.
Code
# Time: 2026-07-04T02:02:07.965410Z
# User@Host: ubb[ubb] @ localhost []  Id: 20276792
# Query_time: 5.692897  Lock_time: 0.000002 Rows_sent: 6  Rows_examined: 1051691
SET timestamp=1783130522;
SELECT p.POST_ID
FROM
{$config_POSTS AS p
LEFT JOIN {$config_POSTS AS pp ON p.POST_ID = pp.POST_ID,
{$config_TOPICS AS t
WHERE
p.POST_IS_APPROVED = '1'
AND t.TOPIC_STATUS <> 'M'
AND t.FORUM_ID IN ('1','2','3','4','5','39','7','9','10','11','85','13','14','15','16','17','18','19','20','21','22','91','90','24','26','27','28','29','30','95','33','38','35','36','40','88','45','68','65','54','52','49','55','50','51','53','56','58','60','61','72','89','69','71','73','74','75','76','77','78','81','86','93','92','94')
AND p.TOPIC_ID = t.TOPIC_ID
AND (   (LOWER(p.POST_SUBJECT) LIKE LOWER('%spare lock%') OR LOWER(p.POST_DEFAULT_BODY) LIKE LOWER('%spare lock%'))) 
 
 
 
 
ORDER BY p.POST_POSTED_TIME DESC
LIMIT 400;[/
I offer these as suggestions for improving performance of the forums and not as a criticism.
This query is in dosearch..inc.php on line 548.
The problem is that LIKE '%term%' (with leading wildcard) cannot use normal indexes — so it forces a full table scan
A possible fix is to add a FULLTEXT search index to POST_SUBJECT and POST_DEFAULT_BODY.
E.G.
SQL Query
ALTER TABLE {$config_POSTS 
ADD FULLTEXT idx_search (POST_SUBJECT, POST_DEFAULT_BODY);
You might want to also consider adding similar indexes for POST_IS_APPROVED, POST_POSTED_TIME as well as TOPIC_ID, POST_IS_APPROVED.
SQL Query
-- On POSTS
ALTER TABLE {$config_POSTS ADD INDEX idx_approved_time (POST_IS_APPROVED, POST_POSTED_TIME);
ALTER TABLE {$config_POSTS ADD INDEX idx_topic_approved (TOPIC_ID, POST_IS_APPROVED);

-- On TOPICS
ALTER TABLE {$config_TOPICS ADD INDEX idx_forum_status (FORUM_ID, TOPIC_STATUS);
Then the query would need to use MATCH AGAINST to take advantage of these new indexes.
So, instead of this:
PHP Code
$andquery .= "$andconcat (LOWER(p.POST_SUBJECT) LIKE LOWER('%$word%') ... )"
You would use someting like this:
PHP Code
$match_terms = "AND MATCH(p.POST_SUBJECT, p.POST_DEFAULT_BODY) AGAINST ('$words_escaped' IN BOOLEAN MODE)";
Then, for a full search, you cold use something like this:
PHP Code
$match_terms = "AND MATCH (p.POST_SUBJECT, p.POST_DEFAULT_BODY) AGAINST ('$words_escaped' IN BOOLEAN MODE)";

These changes would likely reduce the search query time from almost 6 seconds to under 1 sectond.

The Stovebolt Geek
https://www.stovebolt.com/ubbthreads/ubbthreads.php

Server Information
UBB.threads Version 8.0.0
Release 20240826
Server OS Linux
Server Load 0.11
Web Server Apache/2.4.37
PHP Version 8.3.11
MYSQL Version 8.0.39
Database Size 1.82 GB