Opened 4 years ago
Closed 4 years ago
#3408 closed defect (bug) (fixed)
Adjust bbp_get_child_counts so it can hit index
Reported by: |
|
Owned by: |
|
---|---|---|---|
Milestone: | 2.6.6 | Priority: | normal |
Severity: | normal | Version: | 2.6.5 |
Component: | General - Performance | Keywords: | needs-patch needs-testing |
Cc: |
Description
In includes/common/functions.php
function bbp_get_child_counts
can end up running a pretty nasty query against the postmeta table. Here's an example of a query that takes a very long time to run:
EXPLAIN SELECT p.post_type AS type, p.post_status AS status, COUNT( * ) AS count FROM wp_posts AS p LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id AND pm.meta_key = '_bbp_forum_id' WHERE pm.meta_value = 239 GROUP BY p.post_status, p.post_type\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pm type: ref possible_keys: post_id,meta_key,meta_key_meta_val key: meta_key key_len: 767 ref: const rows: 13466616 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: p type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: XXXX.pm.post_id rows: 1 Extra: NULL 2 rows in set (0.01 sec)
It sucks to be querying on meta_value
at all, but setting that aside, we can create an index on the postmeta to at least help, by indexing (meta_key, meta_value
). The problem is that this query still does not hit the index because you are passing an integer to be queried when the type of the meta_value
column is VARCHAR
.
If you pass this as a string instead, the index can be hit:
EXPLAIN SELECT p.post_type AS type, p.post_status AS status, COUNT( * ) AS count FROM wp_posts AS p LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id AND pm.meta_key = '_bbp_forum_id' WHERE pm.meta_value = '239' GROUP BY p.post_status, p.post_type\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pm type: ref possible_keys: post_id,meta_key,meta_key_meta_val key: meta_key_meta_val key_len: 770 ref: const,const rows: 55 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: p type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: XXXX.pm.post_id rows: 1 Extra: NULL 2 rows in set (0.00 sec)
This results in the query running many orders of magnitudes faster when an index has been applied. I am not asking you to add an index to postmeta, but please query meta_value using string values when you do, so that indexes can be added to help.
In 7149: