Comments (4)
Sure, ps_facetedsearch was not done with performance in mind...
from prestashop.
Hi @ShaiMagal
Thank for your report,
This issue is too technical for me, ping @PrestaShop/tech-council , can someone please reproduce this issue ?
Thanks
from prestashop.
Yep, that's a killer. Category with 4 thousand products, total load time 435 ms and half of this are those two queries. And also that query for specific prices, which I don't know what it's is purpose honestly.
Query | Time (ms) | Rows | Location |
---|---|---|---|
SELECT SQL_NO_CACHE p.id_product FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales, p.on_sale, p.date_add FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category) WHERE ps.id_shop='1' AND ps.visibility IN ('both', 'catalog') AND cg.id_group='1' AND c.nleft>=46 AND c.nright<=151 GROUP BY p.id_product) p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) GROUP BY p.id_product ORDER BY p.quantity DESC, p.id_product DESC LIMIT 0, 24 | 97.1 | 2950662400 | /modules/ps_facetedsearch/src/Adapter/MySQL.php:86 |
SELECT SQL_NO_CACHE COUNT(DISTINCT p.id_product) c FROM (SELECT p.id_product, p.id_manufacturer, SUM(sa.quantity) as quantity, p.condition, p.weight, p.price, psales.quantity as sales, p.on_sale, p.date_add FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute) LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute AND sa.id_shop = 1 AND sa.id_shop_group = 0 ) LEFT JOIN ps_product_sale psales ON (psales.id_product = p.id_product) INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1 AND ps.active = TRUE) INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product) INNER JOIN ps_category c ON (cp.id_category = c.id_category AND c.active=1) LEFT JOIN ps_category_group cg ON (cg.id_category = c.id_category) WHERE ps.id_shop='1' AND ps.visibility IN ('both', 'catalog') AND cg.id_group='1' AND c.nleft>=46 AND c.nright<=151 GROUP BY p.id_product) p | 85.1 | 737665600 | /modules/ps_facetedsearch/src/Adapter/MySQL.php:86 |
SELECT SQL_NO_CACHE COUNT(DISTINCT id_product ) FROM ps_specific_price WHERE id_product != 0 LIMIT 1 |
33.4 | 22777 | /classes/SpecificPrice.php:283 |
from prestashop.
I think, this issue will be solved by PR here:
PrestaShop/ps_facetedsearch#1020
from prestashop.
Related Issues (20)
- Kanbot deployment ! Feature wording
- Upgrade from 8.1.0 to 8.1.6 causing error via 1-Click Upgrade HOT 5
- Improve AddressFormat's getValidateFields method used to filter out id fields HOT 1
- Warning when clearing cache on windows
- Problems with Product Customizations HOT 2
- getOrderShippingCostExternal seems not work HOT 1
- empty abandoned carts with no customers HOT 5
- Error design/mail_theme/preview HOT 1
- Long page load due to category breadcrumb generation in product V2 HOT 2
- Order payment failure HOT 2
- Product: Additional feature field maximum length 255 characters - error message could be more clear HOT 2
- Rounding error in Prestashop invoice 8.1.5. Mismatch by cents HOT 5
- AutoUpgrade - fatal error when configuring v5.0.2 HOT 5
- Package sizes converted to int
- ContextErrorException when accessing a product page with `categories` in the rewrite rule when initializing the category in ProductController HOT 1
- 1-Click Upgrade v5.0.2 HOT 5
- HTTP ERROR 500 HOT 2
- Productcomments: On product update event (updateProduct), some information disappears on the product page HOT 1
- Customized product - Stock > Deny order is not taken into account when out of stock
- Release 8.1.7 🌳 HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from prestashop.