Forum

Thread tagged as: Problem, Runway, Shop

Slow product page load

In my catalog page am loading products like so

perch_shop_products([
'template' => 'list2.html',
'paginate' => true,
'count' => 5
]); 

and the template looks like this

<perch:form id="add_to_cart" app="perch_shop" action="/shop/cart">
<div class="col-lg-4 col-md-4 col-sm-4 col-xs-6">
    <div class="b-item-card">
        <div class="image">
            <a href="<perch:shop id="image" type="image" width="600" height="600" density="1.6" crop="true" />" data-gal="prettyPhoto" title="">
<perch:if exists="image"><img src="<perch:shop id="image" type="image" width="600" height="600" density="1.6" crop="true" />" class="img-responsive center-block" alt="<perch:shop id="title" />"></perch:if>
                <div class="image-add-mod">
                    <span class="btn btn-lightbox btn-default-color1 btn-sm">
                        <i class="fa fa-search-plus fa-lg"></i>
                    </span>
                </div>
            </a>
        </div>
        <div class="card-info">
            <div class="caption">
                <div class="name-item">
                    <a class="product-name" href="product-details.html"><perch:shop id="title" /></a>
                    <div class="rating">
                        <span class="star"><i class="fa fa-star"></i></span>
                        <span class="star"><i class="fa fa-star"></i></span>
                        <span class="star"><i class="fa fa-star"></i></span>
                        <span class="star"><i class="fa fa-star"></i></span>
                        <span class="star star-empty"><i class="fa fa-star-o"></i></span>
                        <div class="add-review">
                            <span><span class="review-counter">4</span> Comment(s)</span>
                            <a href="#">Add comment</a>
                        </div>
                    </div>
                </div>

            <perch:if exists="has_variants">     
                <perch:input id="product" type="select" class="variants" options="<perch:shop id="_variant_opts" type="hidden" />" placeholder="Please choose" required="true" />
            <perch:else />
                <perch:input id="product" type="hidden" value="<perch:shop id="productID" type="hidden" />" />
            </perch:if>

                <div class="card-price-block">
                    <span class="price-title">Price</span>
                    <span class="product-price">
                        <perch:shop id="current_price" type="shop_currency_value" />
                    </span>
                </div>
                <div class="product-description">
                    <p>
                        <perch:shop id="descripcion" type="textarea" />
                    </p>
                </div>
            </div>
            <div class="add-buttons">
                <button type="button" class="btn btn-add btn-add-compare"><i class="fa fa-refresh"></i></button>
                <button type="button" class="btn btn-add btn-add-wish"><i class="fa fa-heart-o"></i></button>
                <button type="button" class="btn btn-add btn-add-cart"><i class="fa fa-shopping-cart"></i></button>
                <div class="cart-add-buttons">
                    <button type="button" class="btn btn-cart-color1"><i class="fa fa-shopping-cart"></i> agregar al carrito</button>
                    <perch:input type="cms" />
                </div>
            </div>
        </div>
    </div>
</div>
</perch:form>

Anything wrong so far? loading the catalog page runs the following long query that makes the page take ~10 seconds to load

https://pastebin.com/raw/pRbCecLc

Lexi McGee

Lexi McGee 3 points

  • 4 years ago

Diagnostic

https://pastebin.com/raw/zDV65UEi

and another thing. We might have imported thousands of products using a script to fill perch2_shop_index and perch2_shop_products as there are zero options to mass import products.

Duncan Revell

Duncan Revell 78 points
Registered Developer

I would definitely upgrade to the latest version of shop first - I believe Drew made some changes that improve performance for variants in the latest version.

See if that helps.

Drew McLellan

Drew McLellan 2638 points
Perch Support

What table type is your perch2_shop_index table?

@drew

MyISAM and optimized. Converted from InnoDB to test if speed changed.

After updating perch and perch shop now the debug message is this

https://pastebin.com/raw/Q67ar6bU

Seems like the long query for what it seemed to be all the products disappeared. Still taking 11 secs to load on localhost

Edit -

It's this

SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC LIMIT 5
Drew McLellan

Drew McLellan 2638 points
Perch Support

Seems like the long query for what it seemed to be all the products disappeared.

Sorry, I don't follow. What happened?

Sorry. Compare this

https://pastebin.com/raw/pRbCecLc

to this

https://pastebin.com/raw/Q67ar6bU

Anyways. This query is what makes the product listing page take 10 seconds to load

SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC LIMIT 5
Drew McLellan

Drew McLellan 2638 points
Perch Support

I would stick to MyISAM for these tables if the queries are running slowly. I'm slightly surprised at that one. Can you run the EXPLAIN version?

Yep it's MyISAM atm. Here's the explain https://tools.mariadb.org/ea/yf4c5

Drew McLellan

Drew McLellan 2638 points
Perch Support

That doesn't look like it should be that slow. Where is the 10 second figure coming from? Is that how long it takes when you run the query independent of the page?

Can you try switching the products table to MyISAM also? I believe there's penalty for querying across different storage engines.

Tried switching both perch2_shop_products table and perch_shop_index to MyISAM. It's only a couple of seconds slower.

As stated before. The 10 second figure comes from this query alone.

SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC LIMIT 0, 5

EXPLAIN here https://tools.mariadb.org/ea/yf4c5

skip-template = true doesn't change much.

Drew. want to see a copy of my db?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, that might be helpful!

Drew McLellan

Drew McLellan 2638 points
Perch Support

Try this:

perch_shop_products([ 
    'template' => 'list2.html', 
    'paginate' => true, 
    'count' => 5,
    'filter-mode' => 'legacy-group', 
]); 

Still 10 seconds after 1 or 2 refreshes.

Query is now

SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, productID ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC LIMIT 0, 5
[1] SELECT FOUND_ROWS() AS `count`

Going to try and switch to 64 bit mysql and apache. Increasing innodb buffer pool size to 4gb

Drew McLellan

Drew McLellan 2638 points
Perch Support

For me, that change dropped it to about 0.6 sec.

Apparently something in my my.cnf is mis-configured or something. This only seems to happen in my computer. On a shared hosting the load times are between 0.5 and 3 seconds if you are visiting a product listing page never visited before. So something related to innoDB caching definitely.