Custom Podcast Feed INNER JOIN Causes Large Volume of Temporary Tables

Here is a copy of the explain plan as well:

https://maplegrovepartners.com/public/powerpress_query_explain.jpg

Thank you. Just to add to this, confirming that the order by gets added starting line 179 of powerpress-playlist.php, and a lot of queries I see in the query log the result set is already filtered down to one row making the ORDER BY unnecessary in that instance.

For example, if the build query contains: wp_posts.post_name = “my post title”
you should always expect a single result, and there for not require the order by to be appended.

Hoping we can review the conditionals of when the ORDER BY needs to be appended to the query to minimize its use accordingly to when its actually relevant to the result set.

• This reply was modified 4 years, 4 months ago by maplegrovehosting.

I now have a working root cause and a partial solution.

Root cause: When a /feed/ is generated for an individual post, powerpress does not alter the ORDERBY clause (as there is no filter) to remove the default sort by post_date. Given that its a single post, we can be confident an order by is not required, and we can us the built-in wp API to check for this condition.

function powerpress_posts_orderby($orderby) {
        if(is_single())
        {
                $orderby = '';
        }
        return $orderby;
}
add_filter('posts_orderby', 'powerpress_posts_orderby');

in powerpress.php, line 2188

We’re still seeing the temporary tables getting logged with post_name = ? in the WHERE clause, so there is likely more conditional logic needed in the if to handle other cases that the plugin uses that I haven’t considered yet. The case above seemed representative of the issue at first glance.

As an update while waiting for a developer response, we mitigated impact by leveraging the MySQL Rewriter Plugin to rewrite the queries as follows:

SELECT wp_posts . * FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? ) GROUP BY wp_posts . ID ORDER BY wp_posts . post_date DESC

TO

SELECT wp_posts . * FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? )

and

SELECT SQL_CALC_FOUND_ROWS wp_posts . ID FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? ) GROUP BY wp_posts . ID ORDER BY wp_posts . post_date DESC LIMIT ?,?

TO

SELECT SQL_CALC_FOUND_ROWS wp_posts . ID FROM wp_posts INNER JOIN wp_postmeta AS pp_wp_postmeta ON wp_posts . ID = pp_wp_postmeta . post_id WHERE ? = ? AND wp_posts . post_name = ? AND wp_posts . post_type = ? AND ( pp_wp_postmeta . meta_key = ? )

• This reply was modified 4 years, 3 months ago by maplegrovehosting.
• This reply was modified 4 years, 3 months ago by maplegrovehosting.