Adjust bbp_get_child_counts so it can hit index

[pgpagely]

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.

[johnjamesjacoby]

In 7149:

Meta: always query meta_value by string, not by integer.

This commit swaps "%d" for "%s" when querying by meta_value, even with numeric values. This allows for custom index alterations to perform as intended, and also more closely follows how WordPress core itself operates.

In branches/2.6, for 2.6.6. See #3408.

[johnjamesjacoby]

In 7150:

Meta: always query meta_value by string, not by integer.

This commit swaps "%d" for "%s" when querying by meta_value, even with numeric values. This allows for custom index alterations to perform as intended, and also more closely follows how WordPress core itself operates.

In trunk, for 2.7. Fixes #3408.