PrestaShop Database Performance: Slow Queries in ps_cart & ps

For any PrestaShop store that has been thriving for several years, a familiar performance challenge often emerges: a noticeable slowdown in the back-office order list, abandoned-cart reporting, and even critical checkout steps. Curiously, catalogue pages might remain lightning-fast. This disparity is a tell-tale sign, often pointing directly to your 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 and historical data, leading to significant performance degradation if not properly managed. At Migrate My Shop, we understand that a healthy database is the backbone of a successful e-commerce operation and a smooth migration.

MySQL slow query log analysis and EXPLAIN output for PrestaShop database optimization

The Silent Killer: Unindexed Queries & The Leftmost-Prefix Rule

The core issue stems from how MySQL indexes are utilized. While PrestaShop's default install/data/db_structure.sql provides essential indexes, many reporting queries, CSV exports, and especially third-party modules often generate their own access paths that don't align with these default structures. The critical concept here 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 will often resort to a full table scan, which is incredibly slow on large tables.

Common Traps in PrestaShop's Core Tables:

ps_cart_product: Consider its id_cart_order key: (id_cart, date_add, id_product, id_product_attribute). While id_product is present, it's not the first column. This means a module querying all carts containing a specific product (e.g., WHERE id_product = X) will bypass this index entirely, scanning every single row.
ps_cart: Keys like id_shop (id_shop, date_add) and id_shop_2 (id_shop, date_upd) both start with id_shop. There's no standalone index covering just date_add or date_upd. This severely impacts retention sweeps or reports filtering solely by date, forcing full table scans (unless on MySQL 8.0.13+ which might attempt an index skip scan).
ps_orders: A crucial (invoice_date) index was only introduced in PrestaShop 9.0.0 via PR #31218. This means earlier versions (1.7.x and 8.x) lack this optimization, making invoice-date reporting scans and filesorts notoriously slow. If you're on an older version, this is a prime candidate for a custom index.

Understanding the leftmost-prefix rule is paramount. The columns a query filters and sorts on matter more than the sheer number of indexes a table carries.

Diagnosing Slow Queries: A Step-by-Step Approach

Pinpointing the exact slow queries is the first step towards a solution. Here’s a methodical approach:

1. Enable the MySQL Slow Query Log

This is your most powerful diagnostic tool. In your MySQL server configuration (my.cnf or my.ini), add or modify these lines:

slow_query_log = 1
l
log_queries_not_using_indexes = 1

l> logs queries taking longer than 0.5 seconds. log_queries_not_using_indexes = 1 is crucial for identifying the exact problem. On shared hosting, you'll likely need your host to enable this.



2. Reproduce the Slow Action & Isolate
Perform the slow action in your PrestaShop back office or front office (e.g., loading the orders list, running an abandoned cart report, going through checkout). Note the exact wall-clock time window. Then, review your slow query log, focusing on entries whose SET timestamp= falls within that window. Be wary: a slow checkout might log an unrelated search query, as seen in some cases.

3. Analyze Log Entries: Rows_examined vs. Rows_sent
Don't just look at Query_time. The real insight comes from the ratio of Rows_examined to Rows_sent. If a query examines 300,000 rows to return just three, that's a clear index problem. If it examines roughly what it returns, the issue might be volume or locking, not indexing.
# 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;
In this example, examining over 1.2 million rows to return 4 is a glaring red flag.

4. Leverage EXPLAIN
Run EXPLAIN on the identified slow query. Look for:

type: ALL: Indicates a full table scan.
key: NULL: Confirms no usable index was found.
A rows estimate near the table's total row count.

These three together unequivocally mean no usable index exists for that query.

5. Compare with SHOW INDEX
Once you know the filtered and sorted columns, run SHOW INDEX FROM ps_cart; (and for other relevant tables). Compare the query's columns against existing indexes. What matters is whether the query mentions the first column of any composite index already present.

Implementing Solutions: Strategic Indexing & Maintenance
Once you've diagnosed the culprits, it's time for targeted action. Always perform these steps on a staging copy with production-sized data first!

1. Add Narrow, Specific Indexes
Based on your EXPLAIN analysis, add one narrow index at a time. For the ps_cart_product example above, an index on (id_product) would be highly effective. After adding, re-run EXPLAIN and time the query. Only keep the index if the key changed and the rows estimate dropped significantly.

2. Data Retention & Pruning Abandoned Carts
Row count is a separate problem from indexing, but it exacerbates index issues. Carts past a retention window with no matching ps_orders.id_cart can be safely archived or deleted in batches. This is critical for ps_cart and ps_cart_product. Remember to handle all child tables referencing id_cart in the correct order to avoid orphans. Building the delete set from ps_orders.id_cart ensures you don't accidentally remove carts belonging to real orders.

3. OPTIMIZE TABLE
After significant deletions, OPTIMIZE TABLE can reclaim space and defragment your tables. On MySQL, this maps to ALTER TABLE ... FORCE, which is a full clustered-index rebuild. While online DDL keeps the exclusive lock brief in modern MySQL versions, a very large ps_cart table might still warrant a maintenance window. MariaDB with innodb_defragment (if enabled and available) can pack pages more efficiently without a full rebuild.

Developer Considerations & PrestaShop Specifics

Custom Indexes and Upgrades: PrestaShop upgrade scripts apply a fixed list of core ALTER statements. They don't reconcile your live schema against db_structure.sql. Custom indexes generally survive major upgrades, but it's crucial to re-check with SHOW INDEX after a major jump and keep definitions outside the database for version control.
The 'Re-display cart at login' Feature: Found under Shop Parameters > Customer Settings (PS_CART_FOLLOWING). When enabled, Context::updateCustomer() calls Cart::lastNoneOrderedCart(). This method is uncached and performs a NOT EXISTS against ps_orders with a correlated subselect on ps_cart. For cold sessions without a cart cookie, this can be a significant performance hit on large ps_cart tables. Consider its impact versus its utility.


Proactive database management is not just about fixing problems; it's about building a resilient, high-performing PrestaShop store. For stores considering a migration, optimizing your database beforehand is a critical step to ensure a smooth transition and peak performance on your new platform. At Migrate My Shop, we specialize in ensuring your PrestaShop store is not only ready for migration but also running at its absolute best.

Ready to Optimize Your PrestaShop Database?
Understanding which composite indexes on ps_cart, ps_cart_product, or ps_orders have earned their place in production, and what retention window works for pruning abandoned carts without wrecking reporting, is key to long-term success. If you're struggling with PrestaShop performance or planning a migration and need expert assistance in database optimization, don't hesitate to reach out to Migrate My Shop. We're here to help you unlock your store's full potential.