Slow ajax add to cart for variable products Woocommerce

Question

I have a woocommerce installation that uses ajax to add products to cart, however it takes a really long time to add a product to the cart (7-10 seconds).

I started logging and realized that there are hundreds of SQL queries that are run each time a product is added to cart, and that is what takes all the time. It should be possible to add a product to cart without running so many queries I believe.

I noticed that WPML might be the culprit but I am not super good with SQL.

Is there a more optimal way to add products with AJAX? This is the code I use today:

// AJAX buy button for variable products
function setAjaxButtons() {
   $('.single_add_to_cart_button').click(function(e) {
      var target = e.target;
      loading(); // loading
      e.preventDefault();
      var dataset = $(e.target).closest('form');
      var product_id = $(e.target).closest('form').find("input[name*='product_id']");
      values = dataset.serialize();
        $.ajax({
          type: 'POST',
          url: window.location.hostname+'/?post_typ=product&add-to-cart='+product_id.val(),
          data: values,
          success: function(response, textStatus, jqXHR){
                loadPopup(target); // function show popup
                updateCartCounter();
            },
        });    
      return false;
   });    

}

Here is some of the SQL that is run each time (there are thousands of lines but I cant post all of them):

FROM wp_icl_translations
        WHERE element_id='1354' AND element_type='post_product'
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1354
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1354
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1354
     1935 Query SELECT post_type FROM wp_posts WHERE ID=162
     1935 Query SELECT * FROM wp_posts WHERE ID = 1639 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1639)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1442)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1443)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1444)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1445)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1694 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1694)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1726 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1726)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1603 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1603)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1695 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1695)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1442 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1446 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1446)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1443 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1444 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1445 LIMIT 1
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1441
     1935 Query SELECT trid, language_code, source_language_code
        FROM wp_icl_translations
        WHERE element_id='1441' AND element_type='post_product'
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1441
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1441
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1441
     1935 Query SELECT post_type FROM wp_posts WHERE ID=162
     1935 Query SELECT * FROM wp_posts WHERE ID = 1652 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1652)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1530)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1531)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1532)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1533)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1696 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1696)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1716 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1716)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1577 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1577)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1697 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1697)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1530 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1564 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1564)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1531 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1532 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1533 LIMIT 1

140226 11:34:04 1935 Query

SELECT post_type FROM wp_posts WHERE ID=1529
     1935 Query SELECT trid, language_code, source_language_code
        FROM wp_icl_translations
        WHERE element_id='1529' AND element_type='post_product'
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1529
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1529
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1529
     1935 Query SELECT post_type FROM wp_posts WHERE ID=162
     1935 Query SELECT * FROM wp_posts WHERE ID = 1638 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1638)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1342)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1343)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1344)
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1345)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1698 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1698)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1727 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1727)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1628 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1628)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1699 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1699)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1342 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1352 LIMIT 1
     1935 Query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1352)
     1935 Query SELECT * FROM wp_posts WHERE ID = 1343 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1344 LIMIT 1
     1935 Query SELECT * FROM wp_posts WHERE ID = 1345 LIMIT 1
     1935 Query SELECT post_type FROM wp_posts WHERE ID=1340
     1935 Query SELECT trid, language_code, source_language_code

Answer 1

The indexing of wp_postmeta can be sped up as described here:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Does wp_icl_translations have a composite index on element_id _and element_type? (Either order)

Why in the world, is WP using 2 queries instead of 1 to do this?

SELECT * FROM wp_posts WHERE ID =       1628 LIMIT 1
SELECT post_id, meta_key, meta_value
     FROM wp_postmeta WHERE post_id IN (1628)

Answer 2

In my case, I am using a modal popup with the variable product details, and needed to get the add-to-cart button to work via AJAX instead of being redirected to the product page. Since I'm fairly new to extending WooCommerce's functionality, I used daklock's code from above to derive my solution:

// AJAX Add To Cart button for variable products
    $(document).on('click', '.single_add_to_cart_button', function(e) {

        // stop default action of Add To Cart button
        e.preventDefault();

        // find/set the form in the DOM and the product_id
        var dataset = $(e.target).closest('form');
        var product_id = $(e.target).closest('form').find("input[name*='product_id']");

        // do all your data serialization and make POST the AJAX request
        values = dataset.serialize();
        $.ajax({
          type: 'POST',
          url: '/?post_type=product&add-to-cart='+product_id.val(),
          data: values,
          success: function(response, textStatus, jqXHR){
                console.log('product was added to cart!');
            },
        });    
        return false;
    });

Answer 3

Managed to find the solution after many hours, the problem was that woocommerce did a GET for the index page after adding to cart, which was taking all the time to load. I commented out the redirect and it works superfast now.

in woocommerce-functions.php

if ( $was_added_to_cart ) {

    $url = apply_filters( 'add_to_cart_redirect', $url );

    // If has custom URL redirect there
    if ( $url ) {
        wp_safe_redirect( $url );
        exit;
    }

    // Redirect to cart option
    elseif ( get_option('woocommerce_cart_redirect_after_add') == 'yes' && $woocommerce->error_count() == 0 ) {
        wp_safe_redirect( $woocommerce->cart->get_cart_url() );
        exit;
    }

    // Redirect to page without querystring args
    elseif ( wp_get_referer() ) {
        // Commented the line below
        //wp_safe_redirect( add_query_arg( 'added-to-cart', implode( ',', $added_to_cart ), remove_query_arg( array( 'add-to-cart', 'quantity', 'product_id' ), wp_get_referer() ) ) );
        exit;
    }

}