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.
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
Again, could you please stop posting coding suggestions from AI sources (Grok, CloudFlare AI, etc)... AI sources don't have access to the entirety of the code base to make worthwhile suggestions, we have the same tools to run directly on the stock builds without any 3rd party additions to HTML or PHP coding (either added by a user, developer, or 3rd party tools such as CloudFlare which inject HTML into pages); we regularly run the product through our own IDE tools.
UBB.threads has had had FULLINDEX searches for eons, enable it in your Control Panel (see the attachment) if it is a feature that you would like to use.
The reason it's a slow query during a search is because it's trolling through your hundreds of thousands of posts looking for the search query, it only has impact to the user performing the search.
A search will query all "post_default" cells relating to a Post or Topic (and not the "every single row in the db" as your post claims) as they contain the contents of a post.
Examining the query you're posting about:
Code
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;
- Searches the _POSTS table and the _TOPICS table (left join) - Where the post is approved, the topic status isn't "M" (Moved, vs C/Closed), that is in any open forum you have - It then grabs the topic id that matches the post id - it then searches the subject and default post body fields for your query "spare lock" - it then orders the results via the last posted time - and finally we limit the query to 400 results.
Again, could you please stop posting coding suggestions from AI sources (Grok, CloudFlare AI, etc)... AI sources don't have access to the entirety of the code base to make worthwhile suggestions, we have the same tools to run directly on the stock builds without any 3rd party additions to HTML or PHP coding (either added by a user, developer, or 3rd party tools such as CloudFlare which inject HTML into pages); we regularly run the product through our own IDE tools.
UBB.threads has had had FULLINDEX searches for eons, enable it in your Control Panel (see the attachment) if it is a feature that you would like to use.
I obviously was unaware of this. Don't you think it should be the default setting?
Originally Posted by Gizmo
The reason it's a slow query during a search is because it's trolling through your hundreds of thousands of posts looking for the search query, it only has impact to the user performing the search.
A search will query all "post_default" cells relating to a Post or Topic (and not the "every single row in the db" as your post claims) as they contain the contents of a post.
Then why does the query log say Rows_examined: 1051691?
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
I obviously was unaware of this. Don't you think it should be the default setting?
Users on alternative engines such as INNODB or on other SQL servers might have issues with that as a default setting. It can also add to load times on some servers.
Should be so many results per page of a global search of how many results overall... Not to mention however many rows in your posts database that it could match against; I'm not staring at code to look currently.
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
