PrestaShop慢MySQL查询优化求助：含SQL_CALC_FOUND_ROWS的查询耗时过长

Hey, let's break down how to fix this slow query and address your questions about SQL_CALC_FOUND_ROWS and ORDER BY:

Can You Remove SQL_CALC_FOUND_ROWS? #

It all comes down to your business requirement:

• If your order dashboard doesn’t need to display the total number of matching orders (like "Showing 1-50 of X total"): You can safely remove SQL_CALC_FOUND_ROWS. This clause forces MySQL to calculate the full number of rows that match your filter before applying the LIMIT—a costly operation on large datasets.
• If you do need the total count: Don’t remove it, but we can replace it with a far more efficient alternative (more on that below).

Why Removing ORDER BY Helped (and How to Keep It Fast) #

The ORDER BY a.id_order DESC was probably triggering a full table scan followed by a slow filesort (sorting results in memory or on disk) to get the latest 50 orders. By adding a targeted index, we can eliminate this sorting overhead entirely while keeping the ordering logic intact.

Step-by-Step Optimization Plan #

1. Add a Composite Index to ps_orders #

Create an index on (id_shop, id_order)—this directly supports both your WHERE id_shop IN (...) filter and ORDER BY id_order DESC clause. MySQL can use this index to quickly fetch the latest 50 orders for the specified shops without extra sorting:

CREATE INDEX idx_id_shop_id_order ON ps_orders (id_shop, id_order DESC);

(Note: Including DESC in the index is optional in newer MySQL versions, but it makes the intent clear.)

2. Replace SQL_CALC_FOUND_ROWS with a Separate Count Query #

Instead of making MySQL calculate total rows and fetch your 50 orders in one go, split it into two queries. The count query will be lightning fast because it only needs to hit the ps_orders table (no joins required):

Query 1: Fetch Paged Order Data (keeps ORDER BY, removes SQL_CALC_FOUND_ROWS)

SELECT 
  a.`id_order`, 
  `reference`, 
  `total_paid_tax_incl`, 
  `payment`, 
  a.`date_add` AS `date_add`, 
  a.id_currency, 
  a.id_order AS id_pdf, 
  CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, 
  osl.`name` AS `osname`, 
  os.`color`, 
  carrier.`name` AS `carriername`, 
  country_lang.name as cname, 
  IF(a.valid, 1, 0) badge_success, 
  shop.name as shop_name 
FROM `ps_orders` a 
LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) 
INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery 
INNER JOIN `ps_country` country ON address.id_country = country.id_country 
INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 2) 
INNER JOIN `ps_carrier` carrier ON (carrier.`id_carrier` = a.`id_carrier`) 
LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`) 
LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 2) 
LEFT JOIN `ps_shop` shop ON a.`id_shop` = shop.`id_shop` 
WHERE a.id_shop IN (1, 5, 3, 4) 
ORDER BY a.id_order DESC 
LIMIT 0, 50;

Query 2: Get Total Matching Orders

SELECT COUNT(*) AS total_orders 
FROM `ps_orders` a 
WHERE a.id_shop IN (1, 5, 3, 4);

This count query will use the same idx_id_shop_id_order index we created, so it should execute in milliseconds.

3. Minor Cleanup #

• Remove the redundant 1 AND in your WHERE clause—it doesn’t affect logic but adds unnecessary clutter.
• Double-check that all join columns (like id_customer, id_address, id_carrier) are primary keys or have indexes (they should be in a standard PrestaShop setup, but it’s worth verifying).

Final Results #

With these changes:

• If you don’t need the total count: Your query will run as fast as the 250ms test you did, while still retaining the ORDER BY to show latest orders first.
• If you do need the total count: The two combined queries will still be way faster than the original 2-second query (likely under 500ms total).

内容的提问来源于stack exchange，提问作者rpf