daklock daklock - 1 year ago 157
SQL Question

Slow ajax add to cart for variable products Woocommerce

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 Source

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;
    }

}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download