Slow Query while Caching Posts

I’ve been able to substantially speed up this query and therefore the indexing process by adding a BTREE index on the post_id column of wp_rp4wp_cache. A hash index probably would have worked as well. It seems mysql needs this separate index on the post_id column only to properly use indexes for the previously slow query.

Similarly, these two unoptimized queries delay the “Linking” process:

SELECT COUNT(P.ID) FROM wp_posts P LEFT JOIN wp_postmeta PM ON (P.ID = PM.post_id AND PM.meta_key = 'rp4wp_auto_linked') WHERE 1=1 AND P.post_type IN ('post') AND P.post_status = 'publish' AND PM.post_id IS NULL GROUP BY P.post_status;

SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'rp4wp_auto_linked' ) WHERE 1=1 AND (wp_postmeta.post_id IS NULL) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5;

The previously added wp_posts indexes (ID,post_type,post_status) and (post_type,post_status,ID) are being used, but don’t seem to be enough to prevent mysql from creating temporary tables and using filesort (on the wp_posts table) in order to execute these two queries.

I’ve found adjusting the ppr (post per request?) value in ./classes/hooks/class-hook-ajax-install-link-posts.php up to 50 increases the number of posts linked per query execution and thus speeds up the process by reducing the number of times these two queries run. By increasing this value, each request will take a longer time so you need to take care to avoid timeouts on the requests.

I suspect “ORDER BY wp_posts.post_date” could be removed from the 2nd query as it is not necessary to sort the posts when we will be linking them all anyways.
I also don’t think “GROUP BY P.post_status” is required as part of the first query as the count should remain the same with or without group by.

I hope this perspective can contribute to the improved linking speed of the plugin.

Adding “orderby” + “order” to the function get_not_auto_linked_posts_ids in ./classes/class-related-post-manager.php appears to reduce the time it takes to execute the 2nd query.

public function get_not_auto_linked_posts_ids( $limit ) {
                return get_posts( array(
                        'fields'         => 'ids',
                        'post_type'      => RP4WP_Related_Post_Manager::get_supported_post_types(),
                        'posts_per_page' => $limit,
                        'post_status'    => 'publish',
                        'meta_query'     => array(
                                array(
                                        'key'     => RP4WP_Constants::PM_POST_AUTO_LINKED,
                                        'compare' => 'NOT EXISTS',
                                        'value'   => ''
                                ),
                        ),
                        'orderby' => 'ID',
                        'order' => 'DESC',
                ) );
        }

In this case, we are now ordering by an indexed column (ID) which has a similar result as ordering by post_date

• This reply was modified 3 years ago by lukelol.

Hey @lukelol,

Thanks a lot for your work and optimization advise.

I’ll have a more detailed look at this soon.

Kind Regards,

Barry Kooij

• This reply was modified 2 years, 7 months ago by Barry Kooij.