LONG database queries / slow page loading

tommynay · October 13, 2013

Hello,

My site is running very slow, my hosting company Vidahost have set up DSO and ACP instead of suphp and memcache which are supposed to make the site run quicker? Can anyone help or advise what needs to be done to improve the page speed/bloated database queries? This is what my host has to say:

"This issue isn't related to images slowing your site down but your database structure. There is a query that runs for 20 seconds which returns 8 results - it's just incredibly inefficient programming. I've pushed the MySQL buffers as far as they can safely go on this server and tried to increase the ram to 3 gig without any change, you might need to get a Prestashop developer to take a look at this. You can also ask on the Prestashop forums to see if anyone else has had the same issue - make sure you show them the query that's being run though."

HOST also went on to say:

"I've added various indexes to see if they'll help but they don't I'm afraid. This query just isn't very efficient at all and takes 20 seconds to run: IFNULL is quite a slow operation I believe, and the DATEDIFF won't help either.

To me it looks like the query needs breaking into smaller chunks so that MySQL can handle it more efficiently."

QUERY running on the front page:

Copying to tmp table | SELECT p.*,

product_shop.*, stock.out_of_stock, IFNULL

(stock.quantity, 0) as quantity, MAX

(product_attribute_shop.id_product_attribut

e) id_product_attribute,

product_attribute_shop.minimal_quantity AS

product_attribute_minimal_quantity,

pl.`description`, pl.`description_short`,

pl.`available_now`,

pl.`available_later`, pl.`link_rewrite`,

pl.`meta_description`, pl.`meta_keywords`,

pl.`meta_title`, pl.`name`, MAX

(image_shop.`id_image`) id_image,

il.`legend`, m.`name` AS manufacturer_name,

cl.`name` AS category_default,

DATEDIFF(product_shop.`date_add`, DATE_SUB

(NOW(),

INTERVAL 0

DAY)) > 0 AS new, product_shop.price AS

orderprice

FROM

`ps_category_product` cp

LEFT JOIN

`ps_product` p

ON

p.`id_product` = cp.`id_product`

INNER JOIN

ps_product_shop product_shop

ON (product_shop.id_product

= p.id_product AND product_shop.id_shop =

1)

LEFT JOIN

`ps_product_attribute` pa

ON

(p.`id_product` = pa.`id_product`)

LEFT JOIN

ps_product_attribute_shop

product_attribute_shop

ON

(product_attribute_shop.id_product_attribut

e = pa.id_product_attribute AND

product_attribute_shop.id_shop = 1 AND

product_attribute_shop.`default_on` = 1)

LEFT

JOIN

ps_stock_available stock

ON

(stock.id_product = p.id_product AND

stock.id_product_attribute = IFNULL

(`product_attribute_shop`.id_product_attrib

ute, 0) AND stock.id_shop = 1 )

LEFT JOIN

`ps_category_lang` cl

ON

(product_shop.`id_category_default` =

cl.`id_category`

AND

cl.`id_lang` = 1 AND cl.id_shop = 1 )

LEFT JOIN

`ps_product_lang` pl

ON

(p.`id_product` = pl.`id_product`

AND

pl.`id_lang` = 1 AND pl.id_shop = 1 )

LEFT JOIN

`ps_image` i

ON

(i.`id_product` = p.`id_product`) LEFT JOIN

ps_image_shop image_shop

ON (image_shop.id_image =

i.id_image AND image_shop.id_shop = 1 AND

image_shop.cover=1)

LEFT JOIN

`ps_image_lang` il

ON

(image_shop.`id_image` = il.`id_image`

AND

il.`id_lang` = 1)

LEFT JOIN

`ps_manufacturer` m

ON

m.`id_manufacturer` = p.`id_manufacturer`

WHERE

product_shop.`id_shop` = 1

AND

cp.`id_category` = 2 AND

product_shop.`active` = 1 AND

product_shop.`visibility` IN ("both",

"catalog") GROUP BY product_shop.id_product

ORDER BY cp.`position` ASC

LIMIT 0,8

PLEASE CAN ANYONE ADVISE WHAT TO DO OR HELP ME SOLVE THIS?

Thanks,

Tommy

PascalVG · October 13, 2013

Hi Tommy,

If I'm not mistaken, this is a query that runs to get the 'featured' products on your homepage. If you look at the information that is shown in the featured products (Product Name, short description, price and if the product is 'new') It's a total overkill to get a list of products, with it's manufacturers, all other kind of info like attributes etc, which aren't needed here, and then limit them to 8 to show eight products in homefeatured...

How many products do you have? How many did you add to the 'Home' category? The query gets ALL products connected to the home category, before it limits them (i.e. throws the rest away) to 8. So limiting the ones in Home category might help a little.

If nothing helps, reconsider to use this module...

pascal

tommynay · October 13, 2013

Hi Pascal,

Many thanks for your reply.

I think I have 134 products here:

Catalog

Products

Page 1 / 2 | Display 20 50 100 300 / 134 result(s)

I can't quite believe my developer did not notice this, I have actually 97 products selected to show up on HOME so I will try what you say.

I have two questions for you if you don't mind:

1) If I display featured products without a name/description/price/attributes, etc when a customer clicks on one of the featured products how will they be able to buy the product if it doesn't have any of the attributes/price, etc set?

YOU CAN SEE THE DEMO THEM I'M USING WHICH SHOWS HOW MY FEATURED PRODUCTS ARE DISPLAYED ON MY HOME PAGE: http://alysum.promokit.eu/en/ Each product is linked to it's product section to buy.

2) Are the below settings for the catalog anything related to my problems?

Administration

Menus

Edit

Name:

*

Class:

*

Module:

Icon:

Upload a logo from your computer (.gif, .jpg, .jpeg or .png).

Status:

Show or hide menu.

Parent:

Home Orders Customers Price Rules Shipping Localization Modules Preferences Advanced Parameters Administration Stats Stock Lof Blogs Lof Blogs

Thanks so much for your advise!

Tommy

tommynay · October 13, 2013

Hi Pascal,

Just noticed my copy and paste didn't look correct. What I meant is that under the:

Administration Menus the catalogue is set as home for parent.

Thanks,

Tommy

PascalVG · October 14, 2013

Hi Tommy,

about the menu->Catalogue etc. That's about the menu structure within the back office. This has nothing to do with your products itself, just defines the menu's you use when going through the back office itself. (Normally you would never have to go into this menu, only when you develop/add your own administration menu's etc. which doesn't happen too often, I suppose :-) ) This has no effect on your shop's performance.

About question 1: When you click a product in featured, it loads all information about that product, to show it on the product-detail page, so that is no problem. I only mentioned in my reply above that the query does some 'overkill' with loading all information about the "Home" products", although it only shows the basic info (name, short description, price and image + new/not new), not the full description, metadata, any attributes it may have, manufacturers linked to it etc. etc. AND then dump most of them when limiting to 8 in the end.

There's not so much you can do here, except for limiting the Home featured products in the first place (by adding only those 8 products to the "Home" category),

OR

In the homefeatured.php file you can replace the function call to GetProducts(...) (that now selects the full products info) with a new function you can write, like getBasicProductsInfo() or so, which is similar to getProducts, but leaves out all unnecessary Prod info.

That said, I think you reduced the amount of Home-category products already, as your site loads like normal (no 20+ seconds delay or so), so maybe it's not necessary anymore to worry about it. :-)

Let me know how it's going,

pascal

PascalVG · October 14, 2013

Hi Tommy, just see that the link you sent is not your own site but just a demo site, right? So maybe there's still a performance problem on yours?

Let me know how things go,

pascal

tommynay · October 15, 2013

Hi Pascal,

Thanks a lot for your advise, I will follow your advice in your previous email and let you know how my is after that.

I have just had a reply from the theme developer of Alysum below which was the demo site link I emailed to you. Does what they say this make any sense to you?

"if only following request is slow "//SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute)//" the reason can be "isotope products" or "productCarousel" because only this modules uses function "Category::getProducts" which has that sql request"

I noticed they say ONLY but the thing is that every page loads extremely slow so will do as you say, unless you think they have noticed something different? I thought I would mention that I am using Attribute wizard pro (AWP) module for all my customized products so not sure if this is likely to have added strain to the long DB queries?

My site is in maintenance mode as I need to fix the speed before it goes live and I am using version 1.4.9. Do you think if I upgraded to version 1.5.6 stable it would improve the speed?

Many thanks again,

Tommy

PascalVG · October 15, 2013

Tommy, upgrading a version can come with all kind of problems. Theme may not be fully compatible, changes you/your developers made may be overwritten and have to be reinserted etc. It's always good to upgrade when you have all the time to really test it on a test site, not just before going live :-) So, No, I don't recommend that now...

That said, the theme developers say only, but that is 'only' so when you didn't add any additional modules that may use them, (like featured products). So from their point of view, the only two who use this function are the two modules they mention. If you added any additional one, there may also be a problem.

Please add my IP address, to that I ca have a look (See Pers. Message I sent)

pascal

tommynay · October 19, 2013

Hi Pascal,

Many thanks for the above advice. How much should I expect to pay a dev to upgrade to the stable version?

I added your IP, were you able to view the site? I have reduced the number of featured products to x8 and it works much faster, within 6 seconds to load a page. I am going to try the getBasicProductsInfo code you said to see if it improves the speed even more.

I read this solution for all the JOINS in the code, does this make sense to you as they don't say how to actually adapt the code in which files?

"Hi, i have 55000 items in version 1.3.1.1 and when i use search in FO it take sooo long to get any result (more than 10seconds).

SQL queries in classes/Search.php are not well designed.

They use join on category_product, product and category. Along with paging it sometimes demands full scan of some tables.

You may also find there the loop on eligible products and IN clause whith huge list of items in your case.

I rewrote function Search::find() and added the new field active to the table ps_search_index (this field is calculated regulary).

Now there are only one SQL request in find() function. And it is fast."

HERE IS ANOTHER SOLUTION - What do you think of this?

"I have been working on optimizing the search query in Prestashop, I had a few clients with 50,000 to 200,000 products complain about very very slow search queries (ranging from 45 seconds to 2+ minutes).

I had identified the source of this problem to a certain database query in /classes/Search.php

The slow part is in the "WHERE" clause, when trying to do a "WHERE id_product IN (SELECT .....)".

It seems that doing that type of search is taking mysql a very long time, it is also not very efficient when searching for multiple words.

I have changed the query around so the "SELECT..." inside the IN() statement is now happening above, the aggregated result is then combined into one string (of all the matching product IDs) and the string is used inside the "IN()" statement instead of the "SELECT....".

After applying this change, I saw the speeds go down to 0-3 seconds.

Make sure to keep a copy of your existing /classes/Search.php before using this version.

I also added a couple of variables on lines 133 and 137 for testing purposes, which allow you to price the time it took for the search, and to turn off mysql caching, to get the most accurate speeds

This change is only for PS 1.2.5"

Look forward to get your feedback on all this?

Thanks,

Tommy

tommynay · October 19, 2013

Hi Pascal,

Sorry about this but I can't find the 'get products' part in the code of homefeatured.php to change to 'getbasicproductinfo'.

I have pasted the code here if you can see it?

<p class="title_block">{l s='Featured products' mod='homefeatured'}</p>

{if isset($products) AND $products}

{assign var='liHeight' value=250}

{assign var='nbItemsPerLine' value=4}

{assign var='nbLi' value=$products|@count}

{math equation="nbLi/nbItemsPerLine" nbLi=$nbLi nbItemsPerLine=$nbItemsPerLine assign=nbLines}

{math equation="nbLines*liHeight" nbLines=$nbLines|ceil liHeight=$liHeight assign=ulHeight}

{foreach from=$products item=product name=homeFeaturedProducts}

{math equation="(total%perLine)" total=$smarty.foreach.homeFeaturedProducts.total perLine=$nbItemsPerLine assign=totModulo}

{if $totModulo == 0}{assign var='totModulo' value=$nbItemsPerLine}{/if}

<li class="ajax_block_product {if $smarty.foreach.homeFeaturedProducts.first}first_item{elseif $smarty.foreach.homeFeaturedProducts.last}last_item{else}item{/if} {if $smarty.foreach.homeFeaturedProducts.iteration%$nbItemsPerLine == 0}last_item_of_line{elseif $smarty.foreach.homeFeaturedProducts.iteration%$nbItemsPerLine == 1} {/if} {if $smarty.foreach.homeFeaturedProducts.iteration > ($smarty.foreach.homeFeaturedProducts.total - $totModulo)}last_line{/if}">

<a href="{$product.link}" title="{$product.name|escape:html:'UTF-8'}" class="product_image"><img src="{$link->getImageLink($product.link_rewrite, $product.id_image, 'home_default')}" height="{$homeSize.height}" width="{$homeSize.width}" alt="{$product.name|escape:html:'UTF-8'}" />{if isset($product.new) && $product.new == 1}<span class="new">{l s='New' mod='homefeatured'}</span>{/if}</a>

<p class="s_title_block"><a href="{$product.link}" title="{$product.name|truncate:50:'...'|escape:'htmlall':'UTF-8'}">{$product.name|truncate:35:'...'|escape:'htmlall':'UTF-8'}</a></p>

<div class="product_desc"><a href="{$product.link}" title="{l s='More' mod='homefeatured'}">{$product.description_short|strip_tags|truncate:65:'...'}</a></div>

<div>

<a class="lnk_more" href="{$product.link}" title="{l s='View' mod='homefeatured'}">{l s='View' mod='homefeatured'}</a>

{if $product.show_price AND !isset($restricted_country_mode) AND !$PS_CATALOG_MODE}<p class="price_container"><span class="price">{if !$priceDisplay}{convertPrice price=$product.price}{else}{convertPrice price=$product.price_tax_exc}{/if}</span></p>{else}<div style="height:21px;"></div>{/if}

{if ($product.id_product_attribute == 0 OR (isset($add_prod_display) AND ($add_prod_display == 1))) AND $product.available_for_order AND !isset($restricted_country_mode) AND $product.minimal_quantity == 1 AND $product.customizable != 2 AND !$PS_CATALOG_MODE}

{if ($product.quantity > 0 OR $product.allow_oosp)}

<a class="exclusive ajax_add_to_cart_button" rel="ajax_id_product_{$product.id_product}" href="{$link->getPageLink('cart')}?qty=1&id_product={$product.id_product}&token={$static_token}&add" title="{l s='Add to cart' mod='homefeatured'}">{l s='Add to cart' mod='homefeatured'}</a>

{else}

<span class="exclusive">{l s='Add to cart' mod='homefeatured'}</span>

{/if}

{else}

{/if}

</div>

</li>

{/foreach}

</ul>

</div>

{else}

<p>{l s='No featured products' mod='homefeatured'}</p>

{/if}

</div>

Tommy

tommynay · October 19, 2013

Sorry Pascal, my mistake I was looking in the .tpl file - here is the homefeatured.php code: Please can you tell me which code to edit? Thanks so much.

if (!defined('_PS_VERSION_'))

exit;

class HomeFeatured extends Module

{

private $_html = '';

private $_postErrors = array();

function __construct()

{

$this->name = 'homefeatured';

$this->tab = 'front_office_features';

$this->version = '0.9';

$this->author = 'PrestaShop';

$this->need_instance = 0;

parent::__construct();

$this->displayName = $this->l('Featured Products on the homepage');

$this->description = $this->l('Displays Featured Products in the middle of your homepage.');

}

function install()

{

if (!Configuration::updateValue('HOME_FEATURED_NBR', 8) || !parent::install() || !$this->registerHook('displayHome') || !$this->registerHook('header'))

return false;

return true;

}

public function getContent()

{

$output = '<h2>'.$this->displayName.'</h2>';

if (Tools::isSubmit('submitHomeFeatured'))

{

$nbr = (int)(Tools::getValue('nbr'));

if (!$nbr OR $nbr <= 0 OR !Validate::isInt($nbr))

$errors[] = $this->l('Invalid number of products');

else

Configuration::updateValue('HOME_FEATURED_NBR', (int)($nbr));

if (isset($errors) AND sizeof($errors))

$output .= $this->displayError(implode('<br />', $errors));

else

$output .= $this->displayConfirmation($this->l('Settings updated'));

}

return $output.$this->displayForm();

}

public function displayForm()

{

$output = '

<fieldset><legend><img src="'.$this->_path.'logo.gif" alt="" title="" />'.$this->l('Settings').'</legend>

<p>'.$this->l('In order to add products to your homepage, just add them to the "home" category.').'</p><br />

<label>'.$this->l('Number of products displayed').'</label>

<p class="clear">'.$this->l('The number of products displayed on homepage (default: 10).').'</p>

</div>

<center><input type="submit" name="submitHomeFeatured" value="'.$this->l('Save').'" class="button" /></center>

</fieldset>

</form>';

return $output;

}

public function hookDisplayHeader($params)

{

$this->hookHeader($params);

}

public function hookHeader($params)

{

$this->context->controller->addCss($this->_path.'homefeatured.css', 'all');

}

public function hookDisplayHome($params)

{

$category = new Category(Context::getContext()->shop->getCategory(), (int)Context::getContext()->language->id);

$nb = (int)(Configuration::get('HOME_FEATURED_NBR'));

$products = $category->getProducts((int)Context::getContext()->language->id, 1, ($nb ? $nb : 10));

$this->smarty->assign(array(

'products' => $products,

'add_prod_display' => Configuration::get('PS_ATTRIBUTE_CATEGORY_DISPLAY'),

'homeSize' => Image::getSize(ImageType::getFormatedName('home')),

));

return $this->display(__FILE__, 'homefeatured.tpl');

}

LONG database queries / slow page loading

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in