Slow database access

[azurtem]

Hi

I have noticed significant latency recently with my Prestashop (v8.2.1) hosted by Informaniak (cloud server with SSD storage).

When I try to display a customer's summary, i.e. the page that displays their information, lists their carts, orders, products viewed, addresses, messages, etc, with certain customers (who have placed several hundred orders), it can take several minutes (stopwatched !) to display. Is this normal ?

The same thing happens when I create an order in BO. PS will first list existing carts and previous orders before allowing me to configure the order, which can also take a long time.

Do you have any advice or tips to remedy this, please?

Kind regards,

Yann

Edited December 1 by azurtem (see edit history)

[Mediacom87]

Hi,

This is completely normal and problematic for large stores or stores dedicated to professionals.

For this type of store, I can only recommend my module for daily cleaning of a lot of useless or outdated data.

I will also be adding an option to this module that allows you to delete old orders (which are saved and stored for the legal period of 10 years).

 

 

[El Patron]

this module I've used several times for same exact issue, it works.  

https://www.aurone.com/blog/module-prestashop-archivebox-archiver-les-anciennes-commandes/

 

 

[azurtem]

@Mediacom87 : I already have such a module, which can wreak havoc if used without precautions

@El Patron, Thanks, this seems like a viable solution. Unfortunately Prestashop have been a little shortsighted in their ambitions when it comes to scalability

[Prestashop Addict]

Le 01/12/2025 à 9:04 AM, azurtem a dit :

this seems like a viable solution. Unfortunately Prestashop have been a little shortsighted in their ambitions when it comes to scalability

Lot of SQL queries in PS are not optimized for huge data (history or lot of orders per day). Specially join queries with pagination are very poor. Let me give you a sample:
This query takes several minutes if you have more than 100.000 orders, even on a dedicated database server 😞

SELECT o.`id_order`, o.`reference`, o.`total_paid_tax_incl` AS total_paid, ...
FROM `ps_orders` o 
LEFT JOIN `ps_customer` c ON o.`id_customer` = c.`id_customer` 
LEFT JOIN `ps_address` a ON o.`id_address_invoice` = a.`id_address` 
LEFT JOIN `ps_order_state_lang` b ON b.`id_order_state` = o.`current_state` AND b.`id_lang` = 1 
LEFT JOIN `ps_country` co ON co.`id_country` = a.`id_country` 
LEFT JOIN `ps_state` s ON s.`id_state` = a.`id_state` 
LEFT JOIN `ps_order_cart_rule` ocr ON ocr.`id_order` = o.`id_order` 
LEFT JOIN `ps_cart_rule_lang` cr_lang ON ocr.`id_cart_rule` = cr_lang.`id_cart_rule` AND cr_lang.`id_lang` = 1 
LEFT JOIN `ps_order_detail` od ON od.`id_order` = o.`id_order` 
LEFT JOIN `ps_order_slip_detail` osd ON (od.`id_order_detail` = osd.`id_order_detail`) 
LEFT JOIN `ps_product` p ON p.`id_product` = od.`product_id` 
LEFT JOIN `ps_product_lang` p_lang ON p.`id_product` = p_lang.`id_product` AND p_lang.`id_lang` = 1 AND p_lang.`id_shop` = 1 
WHERE o.`id_shop` = 1 AND o.`current_state` != 0 
GROUP BY o.`id_order` LIMIT 0, 250

optimized query < 8 seconds should be something like:

SELECT o.`id_order`, o.`reference`, o.`total_paid_tax_incl` AS total_paid, ... 
FROM (
    SELECT `id_order`
    FROM `ps_orders`
    WHERE `id_shop` = 1 
        AND `current_state` != 0
    ORDER BY `id_order` DESC
    LIMIT 250 OFFSET 0
) AS filtered_orders
JOIN `ps_orders` o ON filtered_orders.`id_order` = o.`id_order`
LEFT JOIN `ps_customer` c ON o.`id_customer` = c.`id_customer` 
LEFT JOIN `ps_address` a ON o.`id_address_invoice` = a.`id_address` 
LEFT JOIN `ps_order_state_lang` osl ON osl.`id_order_state` = o.`current_state` AND osl.`id_lang` = 1 
LEFT JOIN `ps_country` co ON co.`id_country` = a.`id_country` 
LEFT JOIN `ps_state` s ON s.`id_state` = a.`id_state` 
LEFT JOIN (
    SELECT ocr.`id_order`, GROUP_CONCAT(DISTINCT cr_lang.`name` SEPARATOR ', ') AS distinct_coupons
    FROM `ps_order_cart_rule` ocr 
    LEFT JOIN `ps_cart_rule_lang` cr_lang ON ocr.`id_cart_rule` = cr_lang.`id_cart_rule` AND cr_lang.`id_lang` = 1
    GROUP BY ocr.`id_order`
) coupon_data ON coupon_data.`id_order` = o.`id_order`
LEFT JOIN (
    SELECT od.`id_order`, SUM(osd.`amount_tax_incl`) as refunded
    FROM `ps_order_detail` od 
    LEFT JOIN `ps_order_slip_detail` osd ON od.`id_order_detail` = osd.`id_order_detail`
    GROUP BY od.`id_order`
) refund_sums ON refund_sums.`id_order` = o.`id_order`
LEFT JOIN (
    SELECT 
        od.`id_order`,
        GROUP_CONCAT(
            CONCAT(
                '{"id_product":"', od.`product_id`, 
                '","id_variant":"', od.`product_attribute_id`, 
                '","name":"', REPLACE(COALESCE(pl.`name`, ''), '"', '\\"'), 
                '","price":"', od.`product_price`, 
                '","quantity":"', od.`product_quantity`, '"}'
            ) SEPARATOR ','
        ) AS products_json
    FROM `ps_order_detail` od 
    LEFT JOIN `ps_product_lang` pl ON od.`product_id` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1
    GROUP BY od.`id_order`
) product_data ON product_data.`id_order` = o.`id_order`
ORDER BY o.`id_order` DESC;

 

[azurtem]

Unfortunately, rewriting PrestaShop’s queries would likely be a full-time job.

[El Patron]

In performance investigations, the most consistently overlooked bottleneck is MySQL itself, not PHP, the theme, or front-office code.

Most hosting providers deploy MySQL with very conservative, one-size-fits-all default configurations. These defaults are designed for compatibility and low memory usage across thousands of accounts—not for read/write-heavy eCommerce workloads. As a result, several key performance settings are effectively turned off or set so low that they provide little real benefit.

Even a well-built PrestaShop store will feel slow if:

• MySQL is running close to stock defaults
• InnoDB buffers are undersized
• Query and temp tables are forced to spill to disk
• The database is I/O-bound rather than CPU-bound

I’ve written a short series of practical articles focused specifically on MySQL optimization for PrestaShop, based on real audits and production systems:
https://prestaheroes.com/blogs/mysql-optimization

From a cost-to-benefit perspective, the single biggest win today is storage. Over a long career doing performance work, moving from SATA or network-backed disks to NVMe consistently delivers the largest reduction in query latency. NVMe was once expensive, but it is now common even on mid-range VPS plans.

Before chasing theme tweaks or code-level optimizations, make sure:

• MySQL is tuned for an eCommerce workload
• You are not constrained by disk I/O
• Your database is running on NVMe storage

In many cases, fixing those fundamentals alone resolves “slow database access” issues without touching application code.