Jump to content

LONG database queries / slow page loading


Recommended Posts


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
e) id_product_attribute, 
product_attribute_shop.minimal_quantity AS 
pl.`description`, pl.`description_short`, 
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
    DAY)) > 0 AS new, product_shop.price AS 
`ps_category_product` cp
                                LEFT JOIN 
`ps_product` p
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 = 
                                LEFT JOIN 
`ps_product_attribute` pa
(p.`id_product` = pa.`id_product`)
                                 LEFT JOIN 
e = pa.id_product_attribute AND 
product_attribute_shop.id_shop = 1 AND 
product_attribute_shop.`default_on` = 1)
ps_stock_available stock
(stock.id_product = p.id_product AND 
stock.id_product_attribute = IFNULL
ute, 0) AND stock.id_shop = 1  )
                                LEFT JOIN 
`ps_category_lang` cl
(product_shop.`id_category_default` = 
cl.`id_lang` = 1 AND cl.id_shop = 1 )
                                LEFT JOIN 
`ps_product_lang` pl
(p.`id_product` = pl.`id_product`
pl.`id_lang` = 1 AND pl.id_shop = 1 )
                                LEFT JOIN 
`ps_image` i
(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 
                                LEFT JOIN 
`ps_image_lang` il
(image_shop.`id_image` = il.`id_image`
il.`id_lang` = 1)
                                LEFT JOIN 
`ps_manufacturer` m
m.`id_manufacturer` = p.`id_manufacturer`
product_shop.`id_shop` = 1
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

Share this post

Link to post
Share on other sites

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...



Share this post

Link to post
Share on other sites

Hi Pascal,


Many thanks for your reply.

I think I have 134 products here: 

Catalog separator_breadcrumb.png 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 separator_breadcrumb.png Menus separator_breadcrumb.png Edit

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

 enabled.gif  disabled.gif

Show or hide menu.

 Home Orders Customers Price Rules Shipping Localization Modules Preferences Advanced Parameters Administration Stats Stock Lof Blogs Lof Blogs 
Thanks so much for your advise!

Share this post

Link to post
Share on other sites

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),




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,


Share this post

Link to post
Share on other sites

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,


Share this post

Link to post
Share on other sites

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)


Share this post

Link to post
Share on other sites

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 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?

Share this post

Link to post
Share on other sites

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?


<!-- MODULE Home Featured Products -->
<div id="featured-products_block_center" class="block products_block clearfix">
<p class="title_block">{l s='Featured products' mod='homefeatured'}</p>
{if isset($products) AND $products}
<div class="block_content">
{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}
<ul style="height:{$ulHeight}px;">
{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>
<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>
<span class="exclusive">{l s='Add to cart' mod='homefeatured'}</span>
<div style="height:23px;"></div>
<p>{l s='No featured products' mod='homefeatured'}</p>
<!-- /MODULE Home Featured Products -->

Share this post

Link to post
Share on other sites

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_'))
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;
$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');
Configuration::updateValue('HOME_FEATURED_NBR', (int)($nbr));
if (isset($errors) AND sizeof($errors))
$output .= $this->displayError(implode('<br />', $errors));
$output .= $this->displayConfirmation($this->l('Settings updated'));
return $output.$this->displayForm();
public function displayForm()
$output = '
<form action="'.Tools::safeOutput($_SERVER['REQUEST_URI']).'" method="post">
<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>
<div class="margin-form">
<input type="text" size="5" name="nbr" value="'.Tools::safeOutput(Tools::getValue('nbr', (int)(Configuration::get('HOME_FEATURED_NBR')))).'" />
<p class="clear">'.$this->l('The number of products displayed on homepage (default: 10).').'</p>
<center><input type="submit" name="submitHomeFeatured" value="'.$this->l('Save').'" class="button" /></center>
return $output;
public function hookDisplayHeader($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));
'products' => $products,
'add_prod_display' => Configuration::get('PS_ATTRIBUTE_CATEGORY_DISPLAY'),
'homeSize' => Image::getSize(ImageType::getFormatedName('home')),
return $this->display(__FILE__, 'homefeatured.tpl');

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More