slow backoffice

Prerequisites

• I understand and accept the project's code of conduct.
• I have already searched in existing issues and found no previous report of this bug.

Describe the bug and add attachments

Subject: Performance issue in Order Grid Query Builder (PrestaShop 9.1.4)

Environment

• PrestaShop 9.1.4
• MariaDB / MySQL
• Approximately 61,700 orders
• Approximately 44,800 customer addresses
• Single-shop installation

Problem

The Orders page in Back Office loads very slowly (approximately 25 seconds).

Database analysis showed that the generated query from:

src/Core/Grid/Query/OrderQueryBuilder.php

causes MySQL to choose an inefficient execution plan.

Query Plan

The query generated by OrderQueryBuilder starts from the ps_orders table, but MySQL consistently chooses ps_address as the driving table.

EXPLAIN output:

table: a (ps_address)
rows: ~45,000
Extra: Using temporary; Using filesort

As a result, MySQL scans tens of thousands of addresses before returning only 10 order rows.

Verification

The following simplified query executes in approximately 25 seconds when generated by the standard Order Grid:

SELECT ...
FROM ps_orders o
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
...
ORDER BY o.id_order DESC
LIMIT 10

However, forcing the optimizer to start from ps_orders reduces execution time to milliseconds.

Test query:

SELECT
o.id_order,
o.reference,
o.date_add,
CONCAT(LEFT(cu.firstname,1), '. ', cu.lastname) AS customer
FROM ps_orders o FORCE INDEX (PRIMARY)
LEFT JOIN ps_customer cu ON o.id_customer = cu.id_customer
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
INNER JOIN ps_country c ON a.id_country = c.id_country
INNER JOIN ps_country_lang cl ON c.id_country = cl.id_country
WHERE o.id_shop = 1
ORDER BY o.id_order DESC
LIMIT 10;

Execution time:

0.0005 seconds

Additional Observation

A temporary modification in OrderQueryBuilder.php that caused the generated SQL to behave similarly to a STRAIGHT_JOIN reduced Back Office Orders loading time from:

25 seconds

to:

approximately 2 seconds

without any database changes.

Recommendation

The Order Grid query should be rewritten so that:

1. MySQL is encouraged to start from ps_orders.
2. The latest order IDs are selected first.
3. Joins to address, country, state and customer tables are performed afterwards.

Possible approaches:

• Use a derived table/subquery for recent orders.
• Force join order where appropriate.
• Review optimizer hints for MySQL/MariaDB.
• Avoid query plans that begin with ps_address.

Impact

The issue becomes visible on stores with tens of thousands of orders and addresses and significantly affects Back Office usability.

Orders page loading time improved from approximately 25 seconds to approximately 2 seconds after changing query behavior.

Steps to reproduce

Subject: Performance issue in Order Grid Query Builder (PrestaShop 9.1.4)

Environment

• PrestaShop 9.1.4
• MariaDB / MySQL
• Approximately 61,700 orders
• Approximately 44,800 customer addresses
• Single-shop installation

Problem

The Orders page in Back Office loads very slowly (approximately 25 seconds).

Database analysis showed that the generated query from:

src/Core/Grid/Query/OrderQueryBuilder.php

causes MySQL to choose an inefficient execution plan.

Query Plan

The query generated by OrderQueryBuilder starts from the ps_orders table, but MySQL consistently chooses ps_address as the driving table.

EXPLAIN output:

table: a (ps_address)
rows: ~45,000
Extra: Using temporary; Using filesort

As a result, MySQL scans tens of thousands of addresses before returning only 10 order rows.

Verification

The following simplified query executes in approximately 25 seconds when generated by the standard Order Grid:

SELECT ...
FROM ps_orders o
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
...
ORDER BY o.id_order DESC
LIMIT 10

However, forcing the optimizer to start from ps_orders reduces execution time to milliseconds.

Test query:

SELECT
o.id_order,
o.reference,
o.date_add,
CONCAT(LEFT(cu.firstname,1), '. ', cu.lastname) AS customer
FROM ps_orders o FORCE INDEX (PRIMARY)
LEFT JOIN ps_customer cu ON o.id_customer = cu.id_customer
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
INNER JOIN ps_country c ON a.id_country = c.id_country
INNER JOIN ps_country_lang cl ON c.id_country = cl.id_country
WHERE o.id_shop = 1
ORDER BY o.id_order DESC
LIMIT 10;

Execution time:

0.0005 seconds

Additional Observation

A temporary modification in OrderQueryBuilder.php that caused the generated SQL to behave similarly to a STRAIGHT_JOIN reduced Back Office Orders loading time from:

25 seconds

to:

approximately 2 seconds

without any database changes.

Recommendation

The Order Grid query should be rewritten so that:

1. MySQL is encouraged to start from ps_orders.
2. The latest order IDs are selected first.
3. Joins to address, country, state and customer tables are performed afterwards.

Possible approaches:

• Use a derived table/subquery for recent orders.
• Force join order where appropriate.
• Review optimizer hints for MySQL/MariaDB.
• Avoid query plans that begin with ps_address.

Impact

The issue becomes visible on stores with tens of thousands of orders and addresses and significantly affects Back Office usability.

Orders page loading time improved from approximately 25 seconds to approximately 2 seconds after changing query behavior.

Expected behavior

No response

Actual Result

No response

PrestaShop version where the bug happens

9.1.4

How have you installed PrestaShop

No response

PHP version(s) where the bug happened

8.3.31

Your company or customer's name goes here (if applicable).

www.derks-wielersport.nl