Slow DB queries on large sites

  • Hello,

    We have a big website with thousands of posts. Now these posts have thousands of post meta entries.

    We’re facing slow queries from the following functions and that is also spiking CPU usage:

    does_file_exist_local() – SELECT COUNT ( * ) FROM wp_postmeta WHERE meta_key = ? AND meta_value = ?

    and

    offload_duplicate_items() – SELECT m . post_id FROM wp_postmeta AS m LEFT JOIN wp_posts AS p ON m . post_id = p . ID AND p . post_type = ? WHERE m . meta_key = ? AND m . meta_value = ? AND m . post_id != ? AND m . post_id NOT IN ( SELECT i . source_id FROM wp_as3cf_items AS i WHERE i . source_type = ? AND i . source_id = m . post_id )

    Is there any solution to this? We can’t reduce the number of posts and post meta entries. So either the queries need to be optimized or changed altogether and shifted to a new, properly indexed DB table.

    Thanks

    • This topic was modified 3 months, 3 weeks ago by Ram Shengale.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter Ram Shengale

    (@ramshengale)

    On digging deeper, I think the query in does_file_exist_local() can be run on this table instead: wp_as3cf_items. That’ll fix the slow query issue for uploads.

    SELECT COUNT ( * ) FROM wp_as3cf_items WHERE path = ?

    Also regarding offload_duplicate_items(), here the issue can be resolved by removing m.meta_value = %s part from the query and then checking the path in PHP from the query results.

    Is it possible to incorporate these changes in the plugin?

    Plugin Support Delicious Brains Support

    (@dbisupport)

    Hi Ram,

    WP Offload Media Support Team here, Thanks for reaching out with your query we would be happy to assist.

    As mentioned in your ticket opened through the Support tab of the Pro version of the plugin, these suggested changes have now been mentioned to our dev team. We will be sure to notify you for any updates regarding this.

    Feel free to message us anytime if you need further assistance,
    Thank you.

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.