Some tips on SQL performance optimization for Magento

This article records practical areas to review when a Magento database becomes slow or contributes to production bottlenecks. It reflects the diagnostic work and tooling available when the article was written, so treat it as historical technical context rather than a ready-made production runbook.

Fitting all data in memory

Our typical optimization work started with the output of SHOW STATUS and SHOW GLOBAL VARIABLES, together with the size of the InnoDB buffer pool. A database inventory can begin with a query such as:

SELECT engine,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY engine;

Every large table should be checked and, where appropriate, optimized, reduced or archived. In older installations the InnoDB buffer pool was often left at a default of 128 MB or 256 MB even when Magento’s tables contained much more data. A sizing estimate could be produced with:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

The buffer pool must remain within the available memory limit. A separate database server or cloud instance can prevent MySQL from competing with the web server and PHP for memory, but the right topology depends on the current workload and operational constraints.

Slow queries and performance schema

The next historical diagnostic step was to enable the slow query log or the performance schema and collect data for a meaningful period, often a week or more:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 0;

Collecting every query can expose small, high-frequency operations that create a large aggregate slowdown. A query log can then be parsed, sorted and weighted for a DBA review. The review may identify missing indexes or inefficient extension behavior. In Magento installations, core_url_rewrite was a common focus because storefront requests repeatedly look up URL routing data there.

Query cache

Historically, the query cache was kept deliberately small. The following query shows its related settings:

SHOW GLOBAL VARIABLES LIKE 'query_cache%';

Historical note: A larger query cache can require more time and resources to scan for every lookup. Query-cache behavior depends on the database version; do not apply this recommendation to a current server without checking its documentation.

Thread pool and connection behavior

With Magento, connection behavior depended heavily on the MySQL driver. Modern mysqlnd versions generally provided reasonable defaults, while old PDO drivers could cause problems when keep-alive was disabled or a new connection was forced for every request.

For broader database and infrastructure work, see our SQL performance optimization service at Techpipe.