Slow Query Execution with derived_merge=on (the default)

  • Resolved Christos Chatzaras

    (@cybercr33p)


    6 months, 2 weeks ago

    The query located in ./woocommerce-ajax-filters/includes/faster_recount.php( https://dpaste.com/4EKV44F8Y ) performs poorly when the MySQL optimizer option derived_merge is enabled (default setting). The execution takes several minutes to complete.

    When derived_merge is disabled (SET optimizer_switch='derived_merge=off'), the query completes in approximately 3 seconds.

    However, disabling derived_merge globally in my.cnf (optimizer_switch=derived_merge=off) causes a significant slowdown for other WordPress-related queries, such as WooCommerce REST API calls (e.g., GET /wp-json/wc/v3/products), which perform optimally when derived_mergeis enabled.

    Is any way before you run the query to have something like this?

    SET SESSION optimizer_switch=’derived_merge=off’

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter Christos Chatzaras

    (@cybercr33p)

    6 months, 2 weeks ago

    I tested it, and by adding this to functions.php, I was able to modify the optimizer settings successfully:

    https://dpaste.com/DKB466R9U.txt

    So, can the following be added before your query?

    $wpdb->query(“SET SESSION optimizer_switch=’derived_merge=off'”);

    And this after your query?

    $wpdb->query(“SET SESSION optimizer_switch=’derived_merge=on'”);

    Let me know if you need any further information.

    Plugin Author RazyRx

    (@razyrx)

    6 months, 2 weeks ago

    Hello,

    Thank you for information. Latest version has option that use this fix for query in our plugin.

    Regards,
    Oleg

    Thread Starter Christos Chatzaras

    (@cybercr33p)

    6 months, 1 week ago

    Thank you.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Slow Query Execution with derived_merge=on (the default)’ is closed to new replies.