Jump to content

Slow database queries, identifying and fixing. - questions and answers

chineseboy

By chineseboy
in Core developers

 Share

Recommended Posts

chineseboy Newbie

chineseboy

Posted
Posted

Thanks Tomerg for taking the time to do this. Anything that speeds up the loading times of Prestashop is greatly appreciated. If the default tag cloud module is uninstalled, would the query not happen at all? I would like to know since I have no use for either the New Products module or any Tag Cloud module.

Link to comment
Share on other sites
  • 1 month later...
jeepsterdk Newbie

jeepsterdk

Posted
Posted
No, I modified the original file.

/Kjeld


Hi Kjeld

I too have some trouble with looooong loading time, so I tried to use your product.php. Unfortunately, it didn't help. Do you any other advice?

Thanks for help....you sure have helped me alot already :)
Link to comment
Share on other sites
presta-dyr Enthusiast

presta-dyr

Posted
Posted

If you have access to the SQL server, you can monitor slow queries. In mySQL you can setup what you consider a slow query and see it in mysql-slow.log. On my server I changed my.ini and set:

slow_query_log = 1
long_query_time = 0.5

/Kjeld

Link to comment
Share on other sites
Jaktis Newbie

Jaktis

Posted
Posted

This seems to aim at a similar problem I'm finding so I tried your version of Product.php if I browse my shop in Chrome it looks ok, but in FF I can only see the header, can anyone confirm this problem?

Best regards
Thomas

I found a way to optimize the query. I discovered that the main problem is the category groups. If the user is not logged in or category groups are not even in use, the query can be much faster. I went from 1.2 s to 0.05 s.

First I examine if category groups are relevant:

if ($cookie->id_customer && Db::getInstance()->getRow('SELECT * FROM `'._DB_PREFIX_.'category_group` WHERE `id_group` != 1'))
{
   $productsList = '
       SELECT cp.`id_product`
       FROM `'._DB_PREFIX_.'category_group` cg
       LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
       WHERE cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (SELECT id_group FROM '._DB_PREFIX_.'customer_group WHERE id_customer = '.intval($cookie->id_customer).')');
}
else {
   $result = Db::getInstance()->ExecuteS('
       SELECT p.id_product
       FROM `'._DB_PREFIX_.'product` p
       WHERE p.`active` = 1
       ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
       LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
   $productsList = array();
   foreach ($result as $res) {
       $productsList[] = $res['id_product'];
   }
   $productsList = implode(",", $productsList);
}



Then I use the list in the original query:

$result = Db::getInstance()->ExecuteS('
   SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,
       i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new, 
       (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice 
   FROM `'._DB_PREFIX_.'product` p
   LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($id_lang).')
   LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
   WHERE p.`active` = 1
   AND DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0
   AND p.`id_product` IN ('.$productsList.')
   ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
   LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));




I have attached my version of classes/Product.php.

/Kjeld

Link to comment
Share on other sites
jeepsterdk Newbie

jeepsterdk

Posted
Posted

Hi

I too tried Kjelds product.php and my page looked fine. Until you tried to access the different products, it would come up with an error message. I used my old products.php again, and it works fine again. But, I am certain that it is just me, that can't figure out to do it right. Kjeld is a master in Prestashop and his solutions is usually 100% great. Kjeld, enlighten us :)
Have a nice day

This seems to aim at a similar problem I'm finding so I tried your version of Product.php if I browse my shop in Chrome it looks ok, but in FF I can only see the header, can anyone confirm this problem?

Best regards
Thomas

I found a way to optimize the query. I discovered that the main problem is the category groups. If the user is not logged in or category groups are not even in use, the query can be much faster. I went from 1.2 s to 0.05 s.

First I examine if category groups are relevant:

if ($cookie->id_customer && Db::getInstance()->getRow('SELECT * FROM `'._DB_PREFIX_.'category_group` WHERE `id_group` != 1'))
{
   $productsList = '
       SELECT cp.`id_product`
       FROM `'._DB_PREFIX_.'category_group` cg
       LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
       WHERE cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (SELECT id_group FROM '._DB_PREFIX_.'customer_group WHERE id_customer = '.intval($cookie->id_customer).')');
}
else {
   $result = Db::getInstance()->ExecuteS('
       SELECT p.id_product
       FROM `'._DB_PREFIX_.'product` p
       WHERE p.`active` = 1
       ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
       LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
   $productsList = array();
   foreach ($result as $res) {
       $productsList[] = $res['id_product'];
   }
   $productsList = implode(",", $productsList);
}



Then I use the list in the original query:

$result = Db::getInstance()->ExecuteS('
   SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,
       i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new, 
       (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice 
   FROM `'._DB_PREFIX_.'product` p
   LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($id_lang).')
   LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
   WHERE p.`active` = 1
   AND DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0
   AND p.`id_product` IN ('.$productsList.')
   ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
   LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));




I have attached my version of classes/Product.php.

/Kjeld

Link to comment
Share on other sites
jeepsterdk Newbie

jeepsterdk

Posted
Posted

Hi Kjeld

Thank you, but I get this error:

Fatal error: Class 'ObjectModel' not found in /customers/jeepster.dk/jeepster.dk/httpd.www/product.php on line 19


The Product.php I published was for 1.3.0.1 (I should have mentioned that) which at that time was the latest and greatest. Here is a Product.php for 1.2.5.0.

/Kjeld
Link to comment
Share on other sites
jeepsterdk Newbie

jeepsterdk

Posted
Posted

Man, do I feel stupid now.....yeah, I put it in the root. It's now in the classes directory and everything works. And yes, speed is surely increased.
Many thanks Kjeld


I looks like you put Product.php in the root. You should put it in the classes directory.

/Kjeld
Link to comment
Share on other sites
sm3004 Newbie

sm3004

Posted
Posted

Well, I did a bit of testing and realized it's only the index page of the BO is veeeery slow, where PrestaShop's news feed can be found advising of updates and the job position in paris.... If I click on any other tabs, the BO is normal speed. Google Page Speed Test tells me that I should minimize request size to reduce latency. I suspect the RSS feed i causing the dramatic slow down. Anyone else experiencing this recently?

Link to comment
Share on other sites
  • 2 weeks later...
Guest

Guest

Posted
Posted

Both tomerg3 and Kjeld are very professional. I am suffering from the slow downloading of my shop.

Hope all of the above solutions work with mine.

I am using version 1.3.0.6,will it be a matter if I download your attachments and replace version 1.3.0.6's?

Link to comment
Share on other sites
Guest

Guest

Posted
Posted

Hi,

Can u help me with my shop ,my shop is very slow and warned by host suppliers and it will going to be banned if it is not solved


 A technician responded to your ticket with:

   Hello
   We continued monitoring your account, as you can see below:

   %CPU: 32.41
   %MEM: 1.46
   mySQL - 0.9


   jansh0    x    32.41    1.46    0.9
   Top Process    %CPU 173    /usr/local/php5/bin/php-cgi /home/jansh0/public_html/chinabrandshoes/product.php
   Top Process    %CPU 131    /usr/local/php5/bin/php-cgi /home/jansh0/public_html/category.php
   Top Process    %CPU 117    /usr/local/php5/bin/php-cgi /home/jansh0/public_html/chinabrandshoes/index.php

   Please try to keep the TOp Process below 20%, CPU below 2%, memory below 1% and mySQL below 0.1. 
We will continue monitoring for another 24 hours to see if it is stable. Thank you for your cooperation.



Here are 2 websites: [Fake website - Moderated by Patric]


A technician responded to your ticket with:

   Hello
   Dear Customer, Please inform us when you done upgrade or necessary steps for reducing resource usage 
and we will recheck your usage. Right now, your resource usage too high for shared server.

   Top 10 of 798 Total Sites By KBytes
   #     Hits     Files     KBytes     Visits     Hostname
   1     36907     17.02%     35602     21.21%     2050006     63.17%     3     0.19%     crawl-66-249-68-208.googlebot.com

   Please try to disable access to your site for spider and search bots.

   Thank you



Googlebot.com slow down my shop?


I am useing version 1.3.0.6, unfortunately product.php and search.php in the above attachment don't work for me.

I really need your help.

Thank you.

Link to comment
Share on other sites
Jaktis Newbie

Jaktis

Posted
Posted

This is a slow querie that we get from our log-file, I have searched here for a solution but didn't find any.

Thank you in advance!
//Thomas

# User@Host: w136801_kontor[w136801_kontor] @ localhost []
# Query_time: 23.419269  Lock_time: 0.000307 Rows_sent: 5  Rows_examined: 4190
SET timestamp=1273623790;
SELECT p.id_product, pl.`link_rewrite`, pl.`name`, pl.`description_short`, i.`id_image`, il.`legend`, ps.`quantity` AS sales, p.`ean13`, cl.`link_rewrite` AS category
       FROM `ps_product_sale` ps
       LEFT JOIN `ps_product` p ON ps.`id_product` = p.`id_product`
       LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 3)
       LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
       LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 3)
       LEFT JOIN `ps_category_lang` cl ON (cl.`id_category` = p.`id_category_default` AND cl.`id_lang` = 3)
       WHERE p.`active` = 1
       AND p.`id_product` IN (
           SELECT cp.`id_product`
           FROM `ps_category_group` cg
           LEFT JOIN `ps_category_product` cp ON (cp.`id_category` = cg.`id_category`)
           WHERE cg.`id_group` = 1
       )
       GROUP BY p.`id_product`
       ORDER BY sales DESC
       LIMIT 0, 5;

Link to comment
Share on other sites
  • 2 months later...
tomerg3 Apprentice

tomerg3

Posted
Posted
Hi Guys, Im using CPANEL as well but i cant find tmp\mysql_slow_queries folder. I tho its going to be useful to have that one. How do I enable it?


There's no option to turn it on or off (at least not my the cPanels I saw), you should ask your host if they offer it.
Link to comment
Share on other sites
  • 1 month later...
guest* Newbie

guest*

Posted
Posted

My presta is also too slow. Actually I have 9 Kategories with many of sub-categories, till deep-Level 5.I changed the product php and the parsetimne is now reduced form 10 to 6, but this is also not acceptable. I have no products imported now (about 4.000, most of them with attributes). I must fix the parsetime before I import the procducts.

I read also all tipps from Nethercott and also buyed the modul blockcategoriesnc. I doesn't work for me (no categories where found, or it found only one of 9!) and it also do not reduce the parsetime.

I'm only an user and know nothing about sql and php. What shall I do to optimize my queries ?

Link to comment
Share on other sites
guest* Newbie

guest*

Posted
Posted
Check with Rocky about his category module, it should work just fine, and will reduce the load time.

Is the site working faster if you disable the category module?


I have tried. The problem is, he cannot reach my project. I have cloned my domain on another IP and you have to change the hosts-file in windows accessing machine in order to can reach the prestashop. I told him step by step what he has to do but he cannot access, I don't know why...
I'm, using now the blockcategoriy from Kjeld, it was standard on german prestaversion. With disabeling RSS-block Module I was able to speed up the page. The RSS module doesnot work on my server and also it's slows the prestashop significantly. My parsetime is now by 0,01-0,03. So if somebody has the same problem, disable RSS-block.
Link to comment
Share on other sites
  • 2 weeks later...
corto_maltese Newbie

corto_maltese

Posted
Posted
This seems to aim at a similar problem I'm finding so I tried your version of Product.php if I browse my shop in Chrome it looks ok, but in FF I can only see the header, can anyone confirm this problem?

Best regards
Thomas

I found a way to optimize the query. I discovered that the main problem is the category groups. If the user is not logged in or category groups are not even in use, the query can be much faster. I went from 1.2 s to 0.05 s.

First I examine if category groups are relevant:

if ($cookie->id_customer && Db::getInstance()->getRow('SELECT * FROM `'._DB_PREFIX_.'category_group` WHERE `id_group` != 1'))
{
   $productsList = '
       SELECT cp.`id_product`
       FROM `'._DB_PREFIX_.'category_group` cg
       LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
       WHERE cg.`id_group` '.(!$cookie->id_customer ?  '= 1' : 'IN (SELECT id_group FROM '._DB_PREFIX_.'customer_group WHERE id_customer = '.intval($cookie->id_customer).')');
}
else {
   $result = Db::getInstance()->ExecuteS('
       SELECT p.id_product
       FROM `'._DB_PREFIX_.'product` p
       WHERE p.`active` = 1
       ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
       LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
   $productsList = array();
   foreach ($result as $res) {
       $productsList[] = $res['id_product'];
   }
   $productsList = implode(",", $productsList);
}



Then I use the list in the original query:

$result = Db::getInstance()->ExecuteS('
   SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,
       i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new, 
       (p.`price` * ((100 + (t.`rate`))/100) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * ((100 + (t.`rate`))/100) * `reduction_percent` / 100)),0)) AS orderprice 
   FROM `'._DB_PREFIX_.'product` p
   LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
   LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
   LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($id_lang).')
   LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
   LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
   WHERE p.`active` = 1
   AND DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0
   AND p.`id_product` IN ('.$productsList.')
   ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
   LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));




I have attached my version of classes/Product.php.

/Kjeld



Hello everyone, can I make you a question?
This parts of code do I have to paste them anywhere iside product.php or do I have to replace something?
Thank you very much!
Link to comment
Share on other sites
  • 3 weeks later...
joecoolio66 Newbie

joecoolio66

Posted
Posted

I was wondering if anyone could help me diagnose my issue. There seems to be a 15 second delay where my site does nothing before loading ANY page. When it does finally switch pages, it loads very fast. I'm using version 1.2.5

First off, admin works great and fast. I have around 100 products and maybe 15 categories.

I have tried to disable categories and new products blocks. This did not help. I also modified the query in the newproducts function to not check for customer groups. Nothing there either.

I have tried the smarty adjustments with cache. No help.

I have talked to my host and there doesn't seem to be an issue as I have other sites on my server which run fast (one is wordpress based).

My site is www.canadacardworld.com. If anyone has any suggestions, they would be welcomed.

Link to comment
Share on other sites
  • 2 weeks later...
mytheory. Newbie

mytheory.

Posted
Posted

Tomer,

Your first post states that your client at the time was only using the default group... so your updated query wouldn't work if we had more than 1 (default) group, correct? Our other groups employ very few people, but a necessary part of our business. So just curious as to if this would help wiht a setup of 4 groups.

Thanks!

Link to comment
Share on other sites
tomerg3 Apprentice

tomerg3

Posted
Posted
Tomer,

Your first post states that your client at the time was only using the default group... so your updated query wouldn't work if we had more than 1 (default) group, correct? Our other groups employ very few people, but a necessary part of our business. So just curious as to if this would help wiht a setup of 4 groups.

Thanks!


Is won't calculate the new products for those groups correctly (I believe they will see general new products), but everything else should work
Link to comment
Share on other sites
goodpeeps Newbie

goodpeeps

Posted
Posted

Can anyone help with this problem. It usually happens when certain users are logged in. When I go to checkout, it takes a very long time to go from address->carrier and takes even longer to go to payment. Here is the query.

# Query_time: 37.425505 Lock_time: 0.014241 Rows_sent: 3 Rows_examined: 332273
SELECT DISTINCT cp.`id_product`
FROM `ps_category_group` cg
INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
WHERE c.`active` = 1 AND p.`active` = 1
AND cg.`id_group` = 1
AND p.id_product IN (
SELECT id_product
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 1
AND sw.word LIKE '20%'
) AND p.id_product IN (
SELECT id_product
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 1
AND sw.word LIKE 'gal%'
) AND p.id_product IN (
SELECT id_product
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 1
AND sw.word LIKE 'reservoir%'
) AND p.id_product IN (
SELECT id_product
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 1
AND sw.word LIKE 'lid%'
)

Link to comment
Share on other sites
goodpeeps Newbie

goodpeeps

Posted
Posted

Hello Tomer,

I have 5100 products. I don't know what to say, but this always happens when certain existing customers log in and try to check out. Going from the addresses page to carriers takes about 20-30 seconds and going from carriers to payment takes about 40 seconds. I check the slow queries right after and this is what came up. Is there a better way to track down the slow query?

Link to comment
Share on other sites
abmag Newbie

abmag

Posted
Posted

Sorry, my english is very bad but I try :red:

search query is very slow and sometimes finish with error 500

Version is 1.3.2.3

My web hosting manager say me that query is a part of this probleme, but if i execute this query on phpmyadmin there is no probleme. I don't understand :bug: 

SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, i.`id_image`, il.`legend`, t.`rate` FROM `ps_product` p LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1) LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 1) LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax` WHERE (`reduction_price` > 0 OR `reduction_percent` > 0) AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= '2010-11-16 11:11:42' AND `reduction_to` >= '2010-11-16 11:11:42')) AND p.`active` = 1 AND p.`id_product` IN ( SELECT cp.`id_product` FROM `ps_category_group` cg LEFT JOIN `ps_category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` = 1 ) ORDER BY RAND()



Do you think that the product defaut category can is at the source of this ?

Can you help me ?

Thank's

Link to comment
Share on other sites
  • 4 weeks later...
  • 3 months later...
  • 1 month later...
ScubaLessonsInc Newbie

ScubaLessonsInc

Posted
Posted

Actually I figured out the issue.. it was running slow because of symbols in import.. specifically anything except for a minus sign "-", numbers or letters inside the fields "Category Name, Name*, Meta-title, Meta-keywords, Meta-tags, URL rewritten will cause errors such as slowing down the site or the friendly URLs not to work or worse the view button to show product detail will bring up page not found. Simple fix was to re-import my categories and my products after scrubbing out all the symbols in those fields in both import files. I used edit and replace in Open office and it worked rather quickly to get it all cleaned.. I reimported about 500 products at a time the first batch I checked the box delete existing products then after that just finished each until all 10,343 products were loaded.

It worked and my site is now super fast.

Link to comment
Share on other sites
  • 1 month later...
biglama Newbie

biglama

Posted
Posted

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

So i think i need a new DB query in classes/search, but really dont know which one is it.

Can anybody help? Thx :)

Link to comment
Share on other sites
otzy Newbie

otzy

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

If you are familiar with php and sql I'll send you my solution.
Link to comment
Share on other sites
ScubaLessonsInc Newbie

ScubaLessonsInc

Posted
Posted

also some times it helps to reindex your products or go to Preferences > Search and click "Add missing products to index" and then "Re-build entire index"

Choose instant search.. make your min. word length only 1 and based on where your key search phrases are on your products score or rank the weight of each .. if the name has the main searchable words then make the product name weight higher! Don't make them all high or it sort of ruins the whole idea.

Link to comment
Share on other sites
ezakimak Newbie

ezakimak

Posted
Posted

Hi !, could you share your code here please?
I would like to give it a try

Hegards

Hugo

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.

If you are familiar with php and sql I'll send you my solution.
Link to comment
Share on other sites
otzy Newbie

otzy

Posted
Posted
Hi !, could you share your code here please?
I would like to give it a try

Hegards

Hugo

ok, try the file in attachment.
before you try add field active tinyint to the table ps_search_index and create index on this field. You have to set active=1 for active products.
Finally replace in search.php all calls Seach::... with Search_Otzy::.... (actually only function find() was changed)

Search_Otzy.php

Link to comment
Share on other sites
biglama Newbie

biglama

Posted
Posted

For specification, iam workin on new presta with books (for my parents who has bookshop), so i cant change names of books, but its quite ok names, without any special characters. I have csv file and i get these books to the presta throw import function. I create search index in presta admin few times and even try in phpmyadmin optimalize tables, but with original search class is searching take from 10-40 seconds. Both ajax and standard when use search button.

Otzy- your remake dont work :( i try it without the new column in search_index table, even with this column but result is always the same it does not find anything.
When i try it without this column i modified the find function and i delet row where is this column used. I dont need the active column because all my products are active.

presta is instaled here: http://knihy.82ado.net/
and now i modified the search.php which is in root. So now if use ajax finding it use the old Search class and when use Find button in form it use Search_Otzy class.


Iam workin with PHP and mysql more than 5 years, but iam still quite beginner :D

Getting the search query in search class is little bit crazy :D

Link to comment
Share on other sites
otzy Newbie

otzy

Posted
Posted

insert command at line 277 of Search_Otzy.php (before $result = $db->ExecuteS($queryResults);)

print '<!--queryResults='.$queryResults.'-->';



then find sql query on the search results page and try to understand why it returns nothing.

By the way - ajax search is rather fast.

Link to comment
Share on other sites
MahmutGulerce Newbie

MahmutGulerce

Posted
Posted

Thanks otzy;
You are rigth. Native search class must be more optimized. left join right join join join.... Its nearly joins all of the database tables for just a simlpe query.
1.4.x version looks faster. But this speed cant be result of minimized queries.
I took a look the sql queries for upgrade to 1.4. There are many of mysql fix, many new common index keys. I think the most conspicuous innovation is selectable DB engine.

Link to comment
Share on other sites
ScubaLessonsInc Newbie

ScubaLessonsInc

Posted
Posted

I did nothing speical to the code and have over 10,000 products and my search comes back in less than a second with all the results it should. I have several prestashop sites and all do well with this. However I did do one thing carefully. I have entered all the meta keywords, meta descriptions, compelte name, friendly URL and most importantly TAGS!! if you use tags and keywords your in good shape. Also please be sure you have indexed all your products under preferences.. sometimes you have to re-build your entire index and index missing items after imports. That could cause problems if you have not done it. Also in preferences>search you can choose a speedy search option and weight of each paramter to search... using the built in search feature of prestashop is easy if you just follow the guide and it will work well.

Things that slow it down are charachters in the friendly URL field other than numbers, letters or minus signs -
Also not tagging your items or putting in key words

Good luck! I have versions 1.36 to 1.4.25 on my sites and all good with search however the newest is the fastest!

Thanks.

Link to comment
Share on other sites
biglama Newbie

biglama

Posted
Posted

I just checked search class in version 1.4 and its almost the same, there are still many join join join, so it should not be any faster. Later i will check otzy search, why it dont work.

And i really dont understand how can help to fill meta tags and other ...

Link to comment
Share on other sites
otzy Newbie

otzy

Posted
Posted
I know it is not as fast when I don't put them in..

Take it or leave it. Up to you but I have several successful sites that search fast with thousands of products


Your site http://www.toolsupplycenter.com is the biggest at the first glance. But any request takes more than 10 seconds, not only search. So if we decrease the time of search response on 10s it will be ok.
But if you try to search some popular word, "set" for instance - the response time will be about 14-15s.

Try to search something here: http://www.brandwelt.com in order to compare performance.

And I agree with Biglama concerning meta tags and other …
Link to comment
Share on other sites
MahmutGulerce Newbie

MahmutGulerce

Posted
Posted
I just checked search class in version 1.4 and its almost the same, there are still many join join join, so it should not be any faster. Later i will check otzy search, why it dont work.

And i really dont understand how can help to fill meta tags and other ...

i agree and also i said exactly that. Its faster but this speed is not related to sql queries in php code. 1.4 have more common index keys and more optimized. 

select from product_lang ..... where id_lang = "" AND id_product

id_lang and id_product keys cant be primary in product_lang table but they are primary within common. So this keys makes some time-saving for DB engine[/code]
I think the most important point is selecting DB engine at installation. As far as I know MyISAM is faster than InnoDB on
READING tables. But slower than InnoDB for WRITING and slover for READ tables that are frequently being inserted to or updated.
MyISAM is the best choice for just select and display the data.

Link to comment
Share on other sites
biglama Newbie

biglama

Posted
Posted

yep MyIsam is fast for select, and prestashop using this engine (mostly).

Hmmmm, really dont know what or why, but i try otzy search class just now, and it works. Iam so tired to think about where i made mistake before (and now i dont care). But search is quite fast and works nice :) U can check at knihy.82ado.net, for now i print the searchquery :)

Sooo Otzy you are almost god, cause U did that. And when you get work ajax searching then you will be square(god) :D

Link to comment
Share on other sites
otzy Newbie

otzy

Posted
Posted

thanks Biglama

ajax searching is not complicated task.
But I think that the development of proprietary search is not efficient way.

In my plan to install and integrate some free search engine.

Link to comment
Share on other sites
broadway34th Newbie

broadway34th

Posted
Posted

Hi Tina,

First of all, many thanks for sharing your solution!

I have a very similar problem. My freshly installed 1.4.3 has only about 1500 products, and it's taking 5 seconds to load the home page and every product page (with force-compile off and cache on). The individual images and files seem to load pretty fast, but when I open the homepage or click on anything on the store, it just hangs for a few seconds before the page starts coming up. When I remove the products by pointing my store to an empty DB, the store runs much faster. Does this symptom sound familiar..?

Just to make sure I understand your solution clearly, do you mean that I should remove anything other than -, letters, numbers on the fields of the Category under the Catalog tab in back office?

On my Category, I have the following fields:

Name:
Displayed:
Parent Category:
Description:
Image:
Meta Title:
Meta Description:
Meta keywords:
Friendly URL:
Groups access:

This list looks slightly different from the ones you listed, so am wondering if your solution potentially only applies to an older version of Prestashop...
I have lots of commas and "&" in the Category "Name" fields, and I have about 50 categories and subcategories overall.

Also, do you know why having commas and other special charaters might be causing the DB query slow-down..?

Lastly, it seems that your store (http://scubalessonsinc.com/ScubaStore/) is currently down with some syntax error.. Maybe you're working on maintenance..

Thanks and best regards,
Soo

Link to comment
Share on other sites
ScubaLessonsInc Newbie

ScubaLessonsInc

Posted
Posted

Yes, but not just the category field that causes it..

Categories should have no symbols if you can avoid that..
The field that is super critical is URL rewritten or friendly url..

In the product page, just below the category check-box box you will see a line that says "click here to improve your products ranking or somthing like that..

Click it, check for each product (or you can do this in sql) for anything other than letters, numbers or minus sign -
if so remove it and it should speed your site up dramatically.

Link to comment
Share on other sites
  • 3 weeks later...
  • 1 month later...
  • 2 months later...
maxkdiv Newbie

maxkdiv

Posted
Posted

Can someone please help with these slow queries? I got 30K+ products and lots of queries take so long...

 

 

# Query_time: 9.569545 Lock_time: 0.000000 Rows_sent: 70 Rows_examined: 6567603

SET timestamp=1321565911;

SELECT id_product

FROM ps_search_word sw

LEFT JOIN ps_search_index si ON sw.id_word = si.id_word

WHERE sw.id_lang = 5

AND sw.word LIKE

'nike%';

 

 

# Query_time: 12.948740 Lock_time: 0.000000 Rows_sent: 520 Rows_examined: 8756804

SET timestamp=1321565966;

SELECT id_product

FROM ps_search_word sw

LEFT JOIN ps_search_index si ON sw.id_word = si.id_word

WHERE sw.id_lang = 5

AND sw.word LIKE

'shoes%';

Link to comment
Share on other sites
maxkdiv Newbie

maxkdiv

Posted
Posted

Also is there a way to make mandatory search of at least 2 words? I've noticed that if I search for "shoes" it takes so long but if I search for "nike shoes" it takes a lot lot more.... How can I force the search function to run if at least 2 words have been typed in?

Link to comment
Share on other sites
ScubaLessonsInc Newbie

ScubaLessonsInc

Posted
Posted

guys actually one of the biggest things to slow down your search is bad data entry..

In the Meta-name and description fields and such.

 

Note you should not have any <, >, #, =, {, }, or ; symbols AT ALL

 

and specifically in the friendly URL field you should have NOTHING BUT LETTERS, NUMBERS AND THE MINUS SIGN - Any other symbol at all can totally mess up your site speed.

 

I would start there to check your data! This got my sites from a load time of .9 to .1 seconds also my searches from 1.1 to .2 seconds.

 

Next, yes you can require a minimum number of words to search, see in back offices Preferences>Search. That is where you can adjust those settings. You can also "REINDEX" there and index any missing products there too. Both will speed up and better your searches.

 

That should take care of the problem folks! Good luck!

 

Many Tanks,

Tina

Link to comment
Share on other sites
  • 1 year later...
  • 8 months later...
tommynay Newbie

tommynay

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

 

Link to comment
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
 Share
Go to topic listing
×
×
  • Create New...