Title: Slow Query while Caching Posts Last modified: November 17, 2021 --- # Slow Query while Caching Posts * Resolved [lukelol](https://wordpress.org/support/users/lukelol/) * (@lukelol) * [4 years, 6 months ago](https://wordpress.org/support/topic/slow-query-while-caching-posts/) * Hi, I’m facing some slowness while caching posts for the installation of RP. Specifically, this query executes every few seconds while loading in the posts at the “Caching Posts” step. * `SELECT COUNT(p.ID) FROM wp_posts p LEFT JOIN wp_rp4wp_cache w ON w.post_id = p.ID WHERE p.post_type IN ('post') AND p.post_status = 'publish' AND w.post_id IS NULL;` * The site in question has over 50k posts. I’ve temporarily adjusted wp_rp4wp_cache to be a MEMORY table, wp_posts is InnoDB. I’ve attempted to add some additional indexes (ID,post_type,post_status) and (post_type,post_status,ID) in order to speed up the query, but to no avail. I suspect this slow query may be slowing down the index process. I’m curious if there might be other indexes or optimizations that can take place to reduce the server load during this caching process. Viewing 4 replies - 1 through 4 (of 4 total) * Thread Starter [lukelol](https://wordpress.org/support/users/lukelol/) * (@lukelol) * [4 years, 6 months ago](https://wordpress.org/support/topic/slow-query-while-caching-posts/#post-15079307) * 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. * Thread Starter [lukelol](https://wordpress.org/support/users/lukelol/) * (@lukelol) * [4 years, 6 months ago](https://wordpress.org/support/topic/slow-query-while-caching-posts/#post-15079467) * 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. * Thread Starter [lukelol](https://wordpress.org/support/users/lukelol/) * (@lukelol) * [4 years, 6 months ago](https://wordpress.org/support/topic/slow-query-while-caching-posts/#post-15079498) * 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 4 years, 6 months ago by [lukelol](https://wordpress.org/support/users/lukelol/). * Plugin Author [Barry Kooij](https://wordpress.org/support/users/barrykooij/) * (@barrykooij) * [4 years, 1 month ago](https://wordpress.org/support/topic/slow-query-while-caching-posts/#post-15532532) * Hey [@lukelol](https://wordpress.org/support/users/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 4 years, 1 month ago by [Barry Kooij](https://wordpress.org/support/users/barrykooij/). Viewing 4 replies - 1 through 4 (of 4 total) The topic ‘Slow Query while Caching Posts’ is closed to new replies. * ![](https://ps.w.org/related-posts-for-wp/assets/icon-256x256.png?rev=970117) * [Related Posts for WordPress](https://wordpress.org/plugins/related-posts-for-wp/) * [Frequently Asked Questions](https://wordpress.org/plugins/related-posts-for-wp/#faq) * [Support Threads](https://wordpress.org/support/plugin/related-posts-for-wp/) * [Active Topics](https://wordpress.org/support/plugin/related-posts-for-wp/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/related-posts-for-wp/unresolved/) * [Reviews](https://wordpress.org/support/plugin/related-posts-for-wp/reviews/) ## Tags * [index](https://wordpress.org/support/topic-tag/index/) * [slow query](https://wordpress.org/support/topic-tag/slow-query/) * 4 replies * 2 participants * Last reply from: [Barry Kooij](https://wordpress.org/support/users/barrykooij/) * Last activity: [4 years, 1 month ago](https://wordpress.org/support/topic/slow-query-while-caching-posts/#post-15532532) * Status: resolved