How to Solve Slow MySQL Queries Causing 100% CPU and Memory Usage in PrestaShop?

How to Solve Slow MySQL Queries Causing 100% CPU and Memory Usage in PrestaShop

Have you ever noticed your PrestaShop server experiencing extremely high CPU and memory usage—sometimes exceeding 100%—even when your server configuration seems more than adequate (e.g., 16-core CPU and 32GB RAM)? And despite optimizing my.cnf, the problem still persists?

In this article, I’ll walk you through how to debug and resolve slow MySQL queries that lead to such performance bottlenecks. These steps helped us identify and fix the root cause in a real PrestaShop installation.

🔍 Step 1: Enable MySQL Slow Query Log

To diagnose slow queries, start by enabling MySQL’s slow query log. You can do this either at runtime or through the configuration file.

Option 1: Enable at Runtime (for MySQL 5.1.6+)

SET GLOBAL log_slow_queries = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql_slow.log';

Option 2: Enable via my.cnf or my.ini

slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql_slow.log
long_query_time = 5

Then restart MySQL:

sudo systemctl restart mysqld   # or
sudo systemctl restart mariadb

🔍 Step 2: Review the Slow Query Log

Once the log is enabled, monitor the contents of the slow query log (e.g., /var/log/mysql/mysql_slow.log). Identify queries with long execution times and copy them into ChatGPT or your preferred SQL analysis tool for detailed inspection and optimization suggestions.

🛠️ Step 3: Identify Module-Related Bottlenecks

In our case, the slow query originated from the ps_facetedsearch module.

If you are using an older version, update to the latest (currently v4.0) from GitHub. After upgrading:

• Rebuild all indexes
• Clear the module cache

If performance issues persist, continue to the next step.

🛠️ Step 4: Add Missing Indexes

To optimize query performance, ensure that essential indexes exist. The following index significantly improved our query speed:

ALTER TABLE ps_stock_available
ADD INDEX idx_product_stock (
  id_product,
  id_product_attribute,
  id_shop,
  id_shop_group,
  quantity,
  out_of_stock
);

🛠️ Step 5: Optimize Stock Availability Filtering Logic

Open the following file:

/modules/ps_facetedsearch/src/Product/Search.php

Replace the logic around line 232 and 277 with the optimized code below to eliminate costly OR conditions in MySQL:

if (count($filterValues) == 1) {
    if ($filterValues[0] == Availability::NOT_AVAILABLE) {
        $operationsFilter[] = [
            ['quantity', [0], '<='],
            ['out_of_stock', $this->psOrderOutOfStock ? [0] : [0, 2], '='],
        ];
    } elseif ($filterValues[0] == Availability::AVAILABLE) {
        $operationsFilter[] = [
            ['quantity', [0], '>'],
            ['out_of_stock', $this->psOrderOutOfStock ? [1, 2] : [1], '='],
        ];
    } elseif ($filterValues[0] == Availability::IN_STOCK) {
        $operationsFilter[] = [
            ['quantity', [0], '>'],
        ];
    }
} elseif (count($filterValues) == 2) {
    if (in_array(Availability::NOT_AVAILABLE, $filterValues) && in_array(Availability::AVAILABLE, $filterValues)) {
        // No stock filter → show all
    } elseif (in_array(Availability::NOT_AVAILABLE, $filterValues) && in_array(Availability::IN_STOCK, $filterValues)) {
        $operationsFilter[] = [
            ['quantity', [0], '<='],
            ['out_of_stock', $this->psOrderOutOfStock ? [0] : [0, 2], '='],
        ];
        $operationsFilter[] = [
            ['quantity', [0], '>'],
        ];
    } elseif (in_array(Availability::AVAILABLE, $filterValues) && in_array(Availability::IN_STOCK, $filterValues)) {
        $operationsFilter[] = [
            ['quantity', [0], '>'],
        ];
    }
}

🛠️ Step 6: Remove Unnecessary Fields in SQL Generation

In the file:

/modules/ps_facetedsearch/src/Adapter/MySQL.php

Comment out the quantity field in the setSelectFields() call (around line 791):

$this->setSelectFields([
    'id_product',
    'id_manufacturer',
    // 'quantity', // Commented out to reduce query complexity
    'condition',
    'weight',
    'price',
    'sales',
    'on_sale',
    'date_add',
]);

🛠️ Step 7: Disable Quantity Filtering in Field Mapping and Ordering

Still inside MySQL.php, make the following adjustments:

At Line 256 – Comment Out Quantity Field Mapping

// 'quantity' => [
//     'tableName' => 'stock_available',
//     'tableAlias' => 'sa',
//     'joinCondition' => '(p.id_product = sa.id_product AND IFNULL(pac.id_product_attribute, 0) = sa.id_product_attribute' . $stockCondition . ')',
//     'joinType' => self::LEFT_JOIN,
//     'dependencyField' => 'id_attribute',
//     'aggregateFunction' => 'SUM',
//     'aggregateFieldName' => 'quantity',
// ],

At Line 412 – Disable Ordering by Quantity

// $computedQuantityField = $this->computeFieldName('quantity', $filterToTableMapping);
// $byOutOfStockLast = 'IFNULL(' . $computedQuantityField . ', 0) <= 0';

✅ Final Notes

Once all changes are applied:

1. Rebuild the faceted search index again from the PrestaShop admin panel.
2. Clear the cache completely.
3. Monitor MySQL performance with htop, top, or SHOW PROCESSLIST.

🚀 Result

After following the above steps, our server CPU usage dropped significantly and the slow query was eliminated. This resulted in faster page loads, lower server load, and smoother user experience.

If you’re running a PrestaShop store with a large product catalog and experience performance issues, these optimizations can make a noticeable difference.