Jump to content

Zrychlení SQL dotazu v www/classes/Category.php

michaelam

By michaelam
in Obecná diskuze

 Share

Recommended Posts

michaelam Newbie

michaelam

Posted
Posted

Zdravím,

již jsem bezradná, potřebuji, velice pěkně vás tu zde prosím, pomoci optimalizovat SQL dotaz níže.

U jednoho prestashopu se prování 7 až 30 sekund. Parametry jsme odstranili a došlo ke zlepšení. Klíče v mysql tabulkách se zdá že jsou OK.

Lze z toho vyhodit či optimalizovat nějakou část, aby to jelo rychleji ? Např. část s datumem k ničemu nepotřebujeme atd.

Jedná se o soubor: in /data/www/domeny/sbalaval.cz/www/classes/Category.php:738 

Stránka: https://sbalaval.cz/225-recko 

Parametry serveru jsou dobré, jiná presta s 200 000 produkty bez vlastností s mnoha kategoriemi má načtení stránky do 3 sekund.

  

SELECT SQL_CACHE p.*, product_shop.*, 0 as 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 1
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_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` = 225 AND product_shop.`active` = 1 GROUP BY product_shop.id_product ORDER BY p.`id_product` desc
LIMIT 0,12

 

Níže zasílám část s DEBUG módu:

 

Load time: 6774 ms
You'd better run your shop on a toaster
Execution Load time (ms)
config 66
constructor 0
init 28
checkAccess 0
setMedia 9
postProcess 0
initHeader 0
initContent 6546
initFooter 29
display 95
Hook processing: 111 ms / 11.16 Mb
45 methods called in 30 modules
Hook Processing
displayHeader 5.67 Mb in 58 ms
displayFooter 3.91 Mb in 29 ms
displayLeftColumn 0.91 Mb in 12 ms
displayTop 0.52 Mb in 7 ms
displayNav 0.11 Mb in 3 ms
displayBanner 0.04 Mb in 2 ms
displayTopColumn 0.00 Mb in 0 ms
moduleRoutes 0.00 Mb in 0 ms
actionProductListModifier 0.00 Mb in 0 ms
actionFrontControllerSetMedia 0.00 Mb in 0 ms
actionProductListOverride 0.00 Mb in 0 ms
actionDispatcher 0.00 Mb in 0 ms
displayRightColumn 0.00 Mb in 0 ms
DisplayOverrideTemplate 0.00 Mb in 0 ms
displayProductListFunctionalButtons 0.00 Mb in 0 ms
displayProductDeliveryTime 0.00 Mb in 0 ms
displayProductListReviews 0.00 Mb in 0 ms
displayProductPriceBlock 0.00 Mb in 0 ms
Memory peak usage: 35.7 Mb
Execution Memory (Mb) Total (Mb)
config 13.77 14.0
constructor 0.01 14.0
init 4.79 18.8
checkAccess 0.00 18.8
setMedia 0.38 19.1
postProcess 0.00 19.1
initHeader 0.01 19.1
initContent 10.51 29.7
initFooter 3.93 33.8
display 1.80 35.7
Total cache size (in Cache class): 0.00Mb
DB type: DbPDO
SQL Queries: 113 queries
Time spent querying: 6445 ms
Included files: 222
Size of included files: 4.67 Mb
Globals (> 1 Ko only): 2070 Ko
  • odkazyLeva ≈ 1314.5 Ko
  • _MODULES ≈ 342.3 Ko
  • _LANG ≈ 199.6 Ko
  • context ≈ 140 Ko
  • zemeInviaArr ≈ 33.8 Ko
  • _MODULE ≈ 16.7 Ko
  • _SERVER ≈ 10.8 Ko
  • _COOKIE ≈ 3.1 Ko
  • odkazyHlavicka ≈ 2.9 Ko
  • result ≈ 1.6 Ko
Stopwatch (with SQL_NO_CACHE) (total = 113)
Query Time (ms) Rows Filesort Group By Location 
SELECT SQL_CACHE p.*, product_shop.*, 0 as 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 1
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_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` = 225 AND product_shop.`active` = 1 GROUP BY product_shop.id_product ORDER BY p.`id_product` desc
LIMIT 0,12
 5937.116 2044976 Yes   in /data/www/domeny/sbalaval.cz/www/classes/Category.php:738
 
from /controllers/front/CategoryController.php:200
from /controllers/front/CategoryController.php:118
from /tools/profiling/Controller.php:227
from /classes/Dispatcher.php:373
from /index.php:62 
SELECT SQL_CACHE COUNT(cp.`id_product`) AS total
FROM `ps_product` p
INNER JOIN ps_product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN `ps_category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = 225 AND product_shop.`visibility` IN ("both", "catalog") AND product_shop.`active` = 1 LIMIT 1
 473.247 511244     in /data/www/domeny/sbalaval.cz/www/classes/Category.php:657
 
from /controllers/front/CategoryController.php:198
from /controllers/front/CategoryController.php:118
from /tools/profiling/Controller.php:227
from /classes/Dispatcher.php:373
from /index.php:62 
SELECT SQL_CACHE h.id_hook, h.name as h_name, title, description, h.position, live_edit, hm.position as hm_position, m.id_module, m.name, active
FROM `ps_hook` h
INNER JOIN `ps_hook_module` hm ON (h.id_hook = hm.id_hook AND hm.id_shop = 1)
INNER JOIN `ps_module` as m ON (m.id_module = hm.id_module)
ORDER BY hm.position
 4.109 90 Yes   in /data/www/domeny/sbalaval.cz/www/classes/Hook.php:228
 
from /classes/Hook.php:266
from /classes/tax/TaxManagerFactory.php:67
from /classes/tax/TaxManagerFactory.php:47
from /classes/Product.php:2797
from /classes/Product.php:2672
from /classes/Product.php:3934
from /classes/Product.php:4063
from /classes/Category.php:746
from /controllers/front/CategoryController.php:200
from /controllers/front/CategoryController.php:118
from /tools/profiling/Controller.php:227
from /classes/Dispatcher.php:373
from /index.php:62 
SELECT SQL_CACHE c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
FROM `ps_category` c
INNER JOIN ps_category_shop category_shop
ON (category_shop.id_category = c.id_category AND category_shop.id_shop = 1)
LEFT JOIN `ps_category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = 1  AND cl.id_shop = 1 )
WHERE `id_parent` = 225
AND `active` = 1
GROUP BY c.`id_category`
ORDER BY `level_depth` ASC, category_shop.`position` ASC
 2.452 23 Yes Yes in /data/www/domeny/sbalaval.cz/www/classes/Category.php:580
 
from /controllers/front/CategoryController.php:172
from /controllers/front/CategoryController.php:117
from /tools/profiling/Controller.php:227
from /classes/Dispatcher.php:373
from /index.php:62 
SELECT SQL_CACHE s.*, sl.`description`
FROM `ps_supplier` s
LEFT JOIN `ps_supplier_lang` `sl` ON s.`id_supplier` = sl.`id_supplier` AND sl.`id_lang` = 1
INNER JOIN ps_supplier_shop supplier_shop
ON (supplier_shop.id_supplier = s.id_supplier AND supplier_shop.id_shop = 1)
WHERE (s.`active` = 1)
GROUP BY s.id_supplier
ORDER BY  s.`name` ASC
 2.207 1 Yes Yes in /data/www/domeny/sbalaval.cz/www/classes/Supplier.php:124
 
from /controllers/front/CategoryController.php:135
from /tools/profiling/Controller.php:227
from /classes/Dispatcher.php:373
from /index.php:62

 

 

Link to comment
Share on other sites
tuk66 Apprentice

tuk66

Posted
Posted

Problém je v tom prvním SQL dotazu, který se vykonává 6 sekund. To je hodně a příčinou může být chybějící index. Jestli už v originál PrestaShopu anebo zmizel někdy později, to nevím. Také se ten problém mohl objevit až s velkým počtem produktů.

Celé to dokáže osvětlit použití EXPLAIN před dotazem v phpMyAdminu. 

EXPLAIN SELECT SQL_CACHE p.*, product_shop.*, ...

 

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