GD bbPress Toolbox Pro 4.3.5 is a new minor release that deals with a very interesting performance issue related to a function and SQL query for getting the latest posts for some views and widget.
The issue by itself is not too problematic because it will cause problems only in some rare cases: some versions of MySQL and very large database (millions of posts), but regardless, it is important to be fixed for future proofing plugin against performance issues.
But, it is interesting to point out what exactly happens. The plugin uses an SQL query to get posts for the latest posts widget. This query is quite simple:
SELECT ID, post_type, post_parent, post_author FROM wp_posts
WHERE post_type in (‘topic’, ‘reply’) AND post_status in (‘publish’, ‘closed’)
AND post_date_gmt > ‘2016-09-12 12:00:00’
ORDER BY ID DESC LIMIT 0, 1000
It uses post_date_gmt column because the date we want to check against is derived from PHP time() function returning GMT timestamp. So, why not to use WordPress own column to check, right? Wrong. The column post_date_gmt,while holding GMT version of post_date, is useless for any kind of WHERE or JOIN use because it is not indexed. Column post_date is indexed along with the post_type, post_status, and ID, and any query involving these columns will be fast. Add post_date_gmt, and on a large database, this query will be very slow.
There is a 6 years old ticket on WordPress.org to add an index to GMT column too, but as of yet, that has not happened. So, to avoid potential performance problems, the plugin will revert to use post_date column, but the timestamp will be modified with system timezone value before use.
I would like to thank Jan de Sousa from Daily Rush for reporting the issue and taking the time to help me debug and track the problem.
If you notice any problems with the plugin, stop by the support forums to report it. Let me know what you think about the new version, and as always, suggestions are welcome.
