Very Poor Perfomance On Sql Querys [Solved]

[Toni Mármol]

My setup:

 

PrestaShop Version 1.6.1.4

PHP Version 5.6.18 (OK)

MySQL Version 10.1.11-MariaDB (OK)

Memory Limit 128M

Max Execution Time 30s

Smarty Cache enabled

Smarty Compilation never recompile

 

Dedicated server with no load.

 

With the db profiling activated:

 

Query

SELECT SQL_NO_CACHE a.`id_product`, b.`name` AS `name`, `reference`, a.`price` AS `price`, sa.`active` AS `active`
, shop.`name` AS `shopname`, a.`id_shop_default`, image_shop.`id_image` AS `id_image`, cl.`name` AS `name_category`, sa.`price`, 0 AS `price_final`, a.`is_virtual`, pd.`nb_downloadable`, sav.`quantity` AS `sav_quantity`, sa.`active`, IF(sav.`quantity`<=0, 1, 0) AS `badge_danger`
FROM `ps13_product` a 
LEFT JOIN `ps13_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 3 AND b.`id_shop` = 1)
LEFT JOIN `ps13_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0
AND sav.id_shop = 1  AND sav.id_shop_group = 0 )  JOIN `ps13_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)
LEFT JOIN `ps13_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)
LEFT JOIN `ps13_shop` shop ON (shop.id_shop = a.id_shop_default)
LEFT JOIN `ps13_image_shop` image_shop ON (image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default)
LEFT JOIN `ps13_image` i ON (i.`id_image` = image_shop.`id_image`)
LEFT JOIN `ps13_product_download` pd ON (pd.`id_product` = a.`id_product`) 
WHERE 1  
ORDER BY a.`id_product` desc  LIMIT 0, 50

 

Time (ms)

26294.2

 

Rows

20528640

 

Filesort

Yes

 

Location

/classes/controller/AdminController.php:3207

 

 

 

I get 5351 products on the table.

 

 

Tried the same query directly in phpMyAdmin and the time is 24.6610 sec (24661 ms). 

 

 

 

 

 

I have another slow query. The same query, but with the COUNT(*):

 

SELECT SQL_NO_CACHE COUNT(*) AS `ps13_product` 
FROM `ps13_product` a 
LEFT JOIN `ps13_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 3 AND b.`id_shop` = 1)
LEFT JOIN `ps13_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0
AND sav.id_shop = 1  AND sav.id_shop_group = 0 )  JOIN `ps13_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)
LEFT JOIN `ps13_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)
LEFT JOIN `ps13_shop` shop ON (shop.id_shop = a.id_shop_default)
LEFT JOIN `ps13_image_shop` image_shop ON (image_shop.`id_product` = a.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default)
LEFT JOIN `ps13_image` i ON (i.`id_image` = image_shop.`id_image`)
LEFT JOIN `ps13_product_download` pd ON (pd.`id_product` = a.`id_product`) 

WHERE 1 LIMIT 1

 

25 sec query.

 

 

 

 

All the other querys are so fast, under 10ms.

 

 

 

 

Do you have any idea because that query is so slow please? I can't work with the products (add, modify) at the backoffice.

 

 

Edited February 19, 2016 by Toni Mármol (see edit history)

[Toni Mármol]

The shop comes from Prestashop 1.3, constantly upgraded.

 

But I have manually checked all the indexed of these tables and all are ok. The same indexes and keys than a fresh ps 1.6.1.4 install.

[bellini13]

how many rows exist in ps_product?

 

why does the first query return 20528640 rows?

[Toni Mármol]

I don't know how I get that big number. So strange...

 

Here are the numbers of the all tables involved:

 

ps13_product => 5356 rows

ps13_product_lang => 21424 rows

ps13_stock_available => 5351 rows

ps13_product_shop => 5356 rows

ps13_category_lang => 768 rows

ps13_shop => 1 row

ps13_image_shop => 7747 rows

ps13_image => 7747 rows

ps13_product_download => 1 row

Edited February 19, 2016 by Toni Mármol (see edit history)

[Toni Mármol]

Wow... I've just found the problem.

 

I have some missing UNIQUE keys in 3 tables involved. I think it was due to a bad past update or something.

 

But now it goes like a rocket again:

 

Load Time 843 ms - OK... for a shared hosting
Querying Time 259 ms
Queries 561
Memory Peak Usage 40.1 Mb
Included Files 176 files - 4.28 Mb
PrestaShop Cache - Mb
Global vars 1.63 Mb

[jd440]

Hi,

 

I got exactly the same probleme with an upgrade from 1.6.0.9 -> 1.6.1.4

load time booming form 500ms ==> 8000ms

 

Do you know which unique keys was missing?

[tikz]

I have exactly the same problem. Can you explain what you did? What keys were missing on which tables?

[hakeryk2]

You guys should use EXPLAIN before this select query and there You will see which join is taking to much rows to check and then add index key to this coloumn.