🔍 Summary of the Issue
Users report performance issues in the Back Office > Catalog > Products page of PrestaShop where the system executes a very slow SQL query that includes SQL_CALC_FOUND_ROWS
. This causes long load times for product listings.
🔧Technical Details of the Slow Query
- The reported slow query log includes:
SELECT SQL_CALC_FOUND_ROWS ...
-
SQL_CALC_FOUND_ROWS
tells MySQL to calculate how many rows the query would return without LIMIT, so pagination can be displayed. - This behavior is inherent to Symfony grid pagination logic used by PrestaShop.
🐌 Why It's Slow
-
SQL_CALC_FOUND_ROWS
is deprecated (as of MySQL 8.0.17) and performs poorly on large datasets. - It causes MySQL to scan all rows, even if you're only showing a paginated subset.
- When combined with complex JOINs (as in PrestaShop's product grid), it increases execution time significantly.
- Product list queries involve numerous JOINs to pull names, categories, prices, combinations, etc.
📈 Common Symptoms
- Admin product list takes 10+ seconds to load.
- Server CPU spikes during navigation of Back Office product pages.
- Slow response from database, particularly with thousands of products.
💬 Conclusion
The performance issue stems from legacy SQL techniques (SQL_CALC_FOUND_ROWS
) and heavy JOINs in product grid rendering. A core override or patch is ideal for high-product-volume shops, while SQL/index tuning can mitigate symptoms.