Slow product page load

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.

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

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

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

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

https://i.imgur.com/WBTjSFb.png

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?

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

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.