Performance: Optimize _bbp_last_active_time to use an indexed field for very large deployments
|Reported by:||vibol||Owned by:|
|Cc:||vibol, georgemamadashvili@…, wordpress@…, jared@…, stephen@…, nashwan.doaqan@…|
Background ticket: http://bbpress.trac.wordpress.org/ticket/1885
Vibol: For the first query, last_active_time now looks to be the culprit. It's a postmeta lookup with a sort.
JJJ: Meta queries are always going to be a slow like this. There isn't an available index we can use here to avoid the meta query. We *could* hack in repurposing post_date as the freshness time, but we lose the actual date the topic was posted. It would take some rearchitecting to move the original post creation date to postmeta, to make the index available.
Problem: The _bbp_last_active_time query is doing a full table scan and sort against the postmeta table when retrieving forum index pages. The slowdown is linear with size of table scan. Here's an example slow query:
# Time: 120804 19:52:17 # Query_time: 21.460766 Lock_time: 0.000051 Rows_sent: 25 Rows_examined: 313770 SET timestamp=1344127937; SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_parent = 17 AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed') AND (wp_postmeta.meta_key = '_bbp_last_active_time' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 25;
Expected behavior: Should be fast if the data is indexed, but the postmeta table does not have indexes against it.
Guidance: I'm looking for someone to fix this, or, I am happy to put in the time to fix it with guidance on desired approach. This is a non-trivial issue, so someone with more experience in WP/BBP is ideal.