PrestaShop Database Bottlenecks: Unmasking Slow Queries in Cart & Order Tables

PrestaShop Database Bottlenecks: Unmasking Slow Queries in Cart & Order Tables

For any PrestaShop store operating for several years, a common performance challenge emerges: a slowdown in the back-office order list, abandoned-cart reporting, and even checkout steps, while catalogue pages remain fast. This often points directly to the database, specifically the ps_cart, ps_cart_product, and ps_orders tables. These tables, especially ps_cart and ps_cart_product, can accumulate an enormous number of rows from abandoned guest carts, leading to significant performance degradation if not properly managed.

The Root Cause: Indexing and the Leftmost-Prefix Rule

The core issue stems from how MySQL indexes are utilized. While PrestaShop's default db_structure.sql provides essential indexes, many reporting queries, CSV exports, and third-party modules often generate their own access paths that don't align. The critical concept is the leftmost-prefix rule: an index is usable only from its first column inward. If a query filters or sorts on a column not first in a composite index, or one lacking a standalone index, MySQL performs a full table scan.

For instance, ps_cart_product's id_cart_order key is (id_cart, date_add, id_product, id_product_attribute). id_product is present but not first, making this index ineffective for queries solely filtering by id_product. Similarly, ps_cart's keys lack standalone indexes on date_add or date_upd, hindering retention sweeps. The ps_orders table also saw a crucial (invoice_date) index only introduced in PrestaShop 9.0.0, leaving earlier versions (1.7.x and 8.x) vulnerable to slow invoice-date reporting.

Diagnosing Slow Queries: A Step-by-Step Approach

Pinpointing the exact slow query is crucial. Here’s a recommended approach:

1. Enable the MySQL Slow Query Log: Configure your MySQL server. Your host may need to enable it.

   slow_query_log = 1
   l
   log_queries_not_using_indexes = 1

2. Reproduce and Isolate: Perform the slow action on your PrestaShop store and note the time. Examine the slow query log for entries within that window.
3. Analyze Candidates: Sort by Rows_examined against Rows_sent. A query reading many rows to return few indicates an index problem.

   # Query_time: 12.482  Lock_time: 0.000  Rows_sent: 4  Rows_examined: 1284339
   SET timestamp=1756100000;
   SELECT cp.id_cart, cp.quantity FROM ps_cart_product cp
   WHERE cp.id_product = 4471;

4. Utilize EXPLAIN: Run EXPLAIN on the slow query. type: ALL, key: NULL, and a rows estimate near the table's total indicate no usable index.

   EXPLAIN SELECT cp.id_cart, cp.quantity FROM ps_cart_product cp WHERE cp.id_product = 4471;

5. Compare with Existing Indexes: Use SHOW INDEX FROM ps_cart (and other tables) to compare query columns against existing indexes, remembering the leftmost-prefix rule.
6. Test New Indexes (Cautiously): On a staging environment with production data, add one narrow index. Re-run EXPLAIN and time. Keep only if key changed and rows significantly dropped.

Beyond Indexing: Row Pruning and Other Considerations

Row count is separate from indexing. Regularly prune abandoned cart rows (without a matching ps_orders.id_cart) past a retention window. This can be done in batches, followed by OPTIMIZE TABLE to reclaim space. Be aware that OPTIMIZE TABLE on MySQL performs a full clustered-index rebuild.

Also, the "Re-display cart at login" setting (PS_CART_FOLLOWING) under Shop Parameters > Customer Settings can impact performance. When enabled, Context::updateCustomer() calls Cart::lastNoneOrderedCart(), an uncached query involving a NOT EXISTS and correlated subselect on ps_cart, affecting cold sessions.

Developer Notes for Custom Indexes:

Custom indexes usually survive PrestaShop upgrades, but re-check with SHOW INDEX after a major version jump. Keep custom index definitions outside the database. Any direct deletion of cart rows must handle all child tables referencing id_cart in the correct order to avoid orphaned data.

By understanding these database intricacies and applying a systematic diagnostic approach, PrestaShop merchants and developers can significantly improve the performance and responsiveness of their e-commerce stores.