Inefficient rss channels

[januzi_pl]

This one checked against RC version.

So, I've been getting slow queries in the log and it took me a while to find out where are their source. Here's the query and info:

# Query_time: 1.299403  Lock_time: 0.000046 Rows_sent: 10  Rows_examined: 684884 
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = '_bbp_forum_id' AND CAST(wp_postmeta.meta_value AS SIGNED) = '3' ) 
) AND wp_posts.post_type IN ('reply', 'topic') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

Why is there almost 700k records? Well, the problem is in CAST. Mysql can't use (or refuse to do it) index in the wp_postmeta table. In the case of using CAST explain says:

wp_posts	347933	Using where; Using index; Using temporary; Using filesort
wp_postmeta	1	Using where

(yeah, it shows only half of the rows_examined, but it's still much more than 10 records for the rss, right?).

After removing CAST AS SIGNED:

wp_postmeta	56610	Using where; Using temporary; Using filesort
wp_posts	1	Using where

and the query according to the phpmyadmin is taking 0.18s.

There is no difference in posts IDs between results from the CAST and non CAST queries (mysql 5.1.73). Why there is CAST? I'd say that's the WordPress thing when you pass meta filter:

$meta_query = array( array(
	'key'     => '_bbp_forum_id',
	'value'   => bbp_get_forum_id(),
	'type'    => 'NUMERIC',
	'compare' => '='
	) );

How to fix it.

1. Just compare without casting (need some testing to make sure that this will return proper id's on different mysql versions).
2. Use additional table for connections storage between forum id and topic/reply id (previous versions users would have to run cron/ajax task to fill this table up).
3. Allow to use custom action (or filter?) to get IDs (this way people will be able to overwrite default wordpress query without the worry, that the next update will get rid of the changes made to the core bbpress files).

[johnjamesjacoby]

I've confirmed this as an issue.

It should probably be handled upstream by WordPress core, specifically inside the WP_Meta_Query class.

Going to close this issue as informative.