Critical Performance Issue

Hi,

Thank you for the quick response. Here are the answers to your questions:

1. Are coupons intended to be applied only on cart and checkout pages?

We need auto-apply coupons to be checked on:

• Cart page
• Checkout page
• Any cart-related AJAX requests (add to cart, remove from cart, update cart, apply coupon, remove coupon) regardless of which page they fire from

We do NOT need them checked on regular page loads of homepage, product pages, blog pages, shop pages etc.

2. Are you using any third-party plugin that applies coupons on product pages or other non-cart pages?

No. We are not using any third-party plugin that applies coupons outside of cart/checkout context.

Based on this, please provide the code snippet to restrict auto coupon checking to cart/checkout pages and cart AJAX requests only.

Regarding the Auto-apply coupon settings limit:

We have 171 active auto-apply coupons that are all currently in use and cannot be reduced. The performance issue stems from all 171 coupons being validated on every single page load — not just cart pages.

Our PHP slow log confirms:

maybe_auto_apply_on_wp_loaded()
→ auto_apply_coupons()
→ get_available_auto_coupons()
→ is_coupon_valid() × 171 times
→ validate_coupon_usage_limit()
→ wc_get_orders() × 171 times
= 223 DB queries per page load on every page

This results in 3,400+ slow log entries per day causing CPU spikes during peak traffic.

Important — BOGO Ghost Item Issue:

While attempting to fix this ourselves by returning [] from the wt_sc_auto_coupons_list filter on non-cart pages, we encountered a side effect:

When a BOGO coupon is applied and the paid item is removed from the cart, the free gift item remains as a ghost item in the cart. It only disappears when the customer visits the cart page.

We traced this to get_bogo_eligible_qty() with wbte_sc_bogo_apply_once — it always returns the fixed free qty from coupon settings without checking whether eligible paid products still exist in the cart. As a result when the last paid item is removed:

free_products_qty = 1 (fixed from coupon setting, ignores cart)
free_prod_qty_in_cart = 1
1 < 1 = false → reduce_free_product_qty() never fires
→ free item stays as ghost in cart

This is a pre-existing bug in the plugin that becomes visible when remove_unmatched_autocoupons() is not running on every page load.

Here is my code that I tried

add_filter( 'wt_sc_auto_coupons_list', function( $coupons ) {
if ( is_cart() ) return $coupons;
if ( is_checkout() ) return $coupons;
if ( doing_action( 'woocommerce_after_calculate_totals' ) ||
doing_action( 'woocommerce_cart_updated' ) ) return $coupons;
if ( ! wp_doing_ajax() && isset( $_POST['add-to-cart'] ) && '' !== $_POST['add-to-cart'] ) return $coupons;
if ( wp_doing_ajax() ) {
$wc_ajax = isset( $_GET['wc-ajax'] ) ? sanitize_text_field( $_GET['wc-ajax'] ) : '';
$action = isset( $_POST['action'] ) ? sanitize_text_field( $_POST['action'] ) : '';
$get_action = isset( $_GET['action'] ) ? sanitize_text_field( $_GET['action'] ) : '';
$allowed = [
'add_to_cart', 'remove_from_cart', 'restore_cart_item',
'update_cart', 'apply_coupon', 'remove_coupon',
'woocommerce_add_to_cart', 'kures_update_cart_item_details',
];
if ( in_array( $wc_ajax, $allowed ) ||
in_array( $action, $allowed ) ||
in_array( $get_action, $allowed ) ) return $coupons;
}
return [];
} );

Could you please address both issues:

1. Code snippet to restrict auto-apply checking to cart/checkout/AJAX only
2. Fix for the BOGO ghost item bug in get_bogo_eligible_qty() with apply_once

We will share the WooCommerce System Status Report separately.

Thank you.

Hi Team,

I wanted to share a performance finding that may be useful for improving the plugin for all high-volume stores.

Issue 1 — maybe_auto_apply_on_wp_loaded() fires on every page load

The function fires on every single page request including homepage, product pages, blog pages and shop pages — not just cart/checkout. With 171 auto-apply coupons this generates 171+ DB queries on every page load across the entire site.

We fixed this ourselves by adding an early return in the plugin file:

public function maybe_auto_apply_on_wp_loaded() {
    if ( ( isset( $_GET['wc-ajax'] ) && 'update_order_review' === ... ) || isset( $_REQUEST['update_cart'] ) ) {
        return;
    }

    // Added by us
    if ( ! is_cart() && ! is_checkout() && ! wp_doing_ajax() ) {
        return;
    }

    $this->auto_apply_coupons();
}

We suggest this becomes the default behavior in a future release. Or at list you can provide hook/filter here

Issue 2 — String IDs passed to WC_Coupon constructor causing unnecessary SQL queries

In get_available_auto_coupons(), coupon IDs are fetched correctly from the lookup table via prepare_auto_coupons_list(). However $wpdb->get_col() returns IDs as strings not integers.

When these string IDs are passed to new WC_Coupon( $coupon_id ), the WooCommerce constructor sees a string and calls wc_get_coupon_id_by_code() which runs this SQL query:

SELECT ID FROM wp_posts 
WHERE LOWER(post_title) = LOWER('5710315')
AND post_type = 'shop_coupon'
AND post_status = 'publish'

This query searches post_title (coupon code) for a numeric ID — it will never return a result. It is completely wasted. And because it returns empty, WooCommerce does not cache the result, meaning this uncacheable query hits the database on every single request.

This runs 171 times per execution — once per auto-apply coupon.

Root cause:

// prepare_auto_coupons_list() returns strings from wpdb->get_col()
// ["5710315", "5710316" ...] ← strings

// WC_Coupon constructor:
if ( is_int( $data ) )        // ← FALSE for "5710315" 
    → skips SQL, uses ID directly ✅
elseif ( is_string( $data ) ) // ← TRUE, enters here
    → calls wc_get_coupon_id_by_code() → useless SQL ❌

Fix — simply cast IDs to integers before the loop:

$coupon_ids = $this->prepare_auto_coupons_list( $offset, $auto_coupon_check_count );
$coupon_ids = array_map( 'intval', $coupon_ids ); // ← add this one line

This makes is_int() return true → WooCommerce skips the SQL entirely → coupon is loaded directly by ID.

Impact:

Before: 3 DB calls per coupon × 171 = 513 DB calls per execution
After:  1 DB call per coupon × 171 = 171 DB calls per execution
        (most cached in Redis on subsequent requests)

Combined with Issue 1 fix, total coupon-related slow DB queries will be reduced.

We have already applied both fixes on our end. We wanted to share this so it can be properly fixed in the plugin for all users. Also correct me if I miss something or some issue in my code.

Please let us know if you need any additional details.

Thank you.