[ahillmortons]

Hi,

We have been having issues with a bbPress forum we imported into. Our hosts have said we have some slow queries. Is this something we can alter in bbPress settings?

Reply from our hosts below.

Many Thanks,

Andy

We have lots of queries involving posts and postmeta tables that are notably slow:

# User@Host: [removed] @ [127.0.0.1]
# Thread_id: 517 Schema: [removed] QC_hit: No
# Query_time: 10.200091 Lock_time: 0.000118 Rows_sent: 10 Rows_examined: 2017628
# Rows_affected: 0 Bytes_sent: 195
SET timestamp=1699707197;
SELECT SQL_CALC_FOUND_ROWS wp_y1_4_posts.ID
FROM wp_y1_4_posts INNER JOIN wp_y1_4_postmeta ON ( wp_y1_4_posts.ID = wp_y1_4_postmeta.post_id ) INNER JOIN wp_y1_4_postmeta AS mt1 ON ( wp_y1_4_posts.ID = mt1.post_id )
WHERE 1=1 AND (
wp_y1_4_postmeta.meta_key = ‘_bbp_last_active_time’
AND
(
( mt1.meta_key = ‘_bbp_forum_id’ AND CAST(mt1.meta_value AS SIGNED) != ‘675217’ )
)
) AND ((wp_y1_4_posts.post_type = ‘topic’ AND (wp_y1_4_posts.post_status = ‘publish’ OR wp_y1_4_posts.post_status = ‘closed’ OR wp_y1_4_posts.post_status = ‘expired’)))
GROUP BY wp_y1_4_posts.ID
ORDER BY CAST(wp_y1_4_postmeta.meta_value AS DATETIME) DESC
LIMIT 0, 10;

It takes over 10 seconds to execute, examining over 2 million rows but only sending 10. This inefficiency can be due to unoptimized JOIN operations or lack of proper indexing.you can cosider using a database optimization plugin to improve indexing and query efficiency and Implementing a caching solution to reduce database load, especially for frequently accessed data.