Slow query on sites with large wp_options table

  • jesperh95

    (@jesperh95)


    5 months, 2 weeks ago

    Hi,

    This plugins creates a “slow query” (found with Query Monitor) in the back-end on every page load on sites with large wp-options tables. It happens because of this query:

    “SELECT option_value
    FROM wp_options
    WHERE option_name LIKE ‘wc_facebook_background_product_sync_job_%’
    AND ( option_value LIKE ‘%\”status\”:\”processing\”%’ )
    ORDER BY option_id desc”

    The query is located here: “/includes/Framework/Utilities/BackgroundJobHandler.php”, around line 531 (in plugin version 3.5.11)

    It is a related issue to this following support topic from another user, which did not get a response to his last question: https://ww.wp.xz.cn/support/topic/facebook-for-woocommerce-plugin-causing-many-slow-queries-degrading-performance/

    It seems the only way to fix this issue would to be to rewrite the “background-job-handler”, to not use “wp-options”-table and not use “LIKE” in the SQL-query, which creates full table scans. Because this causes a slow query and degregated performance on larger sites with larger “wp_options” tables.

Viewing 1 replies (of 1 total)
  • Thread Starter jesperh95

    (@jesperh95)

    5 months, 2 weeks ago

    Hi again,

    My side btw have 70.000+ entries in wp_options containing “wc_facebook_background_product_sync_job”, and they are all completed. So another “quick-fix” solution would be to remove these, so that above query would be much faster.

    But do you know why these entries stay in wp_options? I think they should be deleted right after the job has been completed?

    Besides, it seems there is some kind of clean-up happening, since the earliest entries are approx 100 days old, and it keeps staying like this. So if this is correct, then another solution could be to set this clean-up window to maybe 7 days instead. Do you know if this is possible?

Viewing 1 replies (of 1 total)

You must be logged in to reply to this topic.