🔍 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

  1. SQL_CALC_FOUND_ROWS is deprecated (as of MySQL 8.0.17) and performs poorly on large datasets.
  2. It causes MySQL to scan all rows, even if you're only showing a paginated subset.
  3. When combined with complex JOINs (as in PrestaShop's product grid), it increases execution time significantly.
  4. 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.