The Database

Why EAV generates so many queries, which MySQL settings matter, how to read the slow query log, and where a read replica fits.

Syntax$connection->select()->from($table, $columns)->where('column = ?', $value)

EAV — entity, attribute, value — stores each product field as a row in a table chosen by its data type: catalog_product_entity_varchar, _int, _decimal, _text, _datetime. Reading twenty attributes for one product means twenty joins or twenty lookups. This is why Magento builds index tables, and why reading an index table instead of the entity is the largest query-level improvement available to you.

Find the queries first

Turn on MySQL's slow query log with long_query_time set low, around 0.1 seconds, and read it. Magento's own log is bin/magento dev:query-log:enable, which records statements with the PHP call stack into var/debug/db.log. Use the MySQL log to find what is slow and Magento's log to find which code issued it.

Settings that matter

innodb_buffer_pool_size is the one to get right: if the working set does not fit, every read touches disk. Then tmp_table_size and max_heap_table_size together, because a temporary table that outgrows either is written to disk, and catalog queries build large temporary tables. The MySQL query cache does not exist in MySQL 8, so ignore tuning advice that mentions it.

Read replicas

Magento routes reads to a replica through db/slave_connection in app/etc/env.php. The entry takes the same name as its master connection, and the indexer connection cannot have one.

Binding

where('entity_id IN (?)', $ids) is shorthand for where($connection->quoteInto('entity_id IN (?)', $ids)). They build the same SQL. A condition with no placeholder is plain concatenation, whatever you pass beside it.

Example

Example · php
<?php
// app/code/Vendor/Perf/Model/IndexedPriceReader.php
declare(strict_types=1);

namespace Vendor\Perf\Model;

use Magento\Framework\App\ResourceConnection;

class IndexedPriceReader
{
    public function __construct(
        private readonly ResourceConnection $resource
    ) {
    }

    public function getFinalPrices(array $skus, int $customerGroupId, int $websiteId): array
    {
        $connection = $this->resource->getConnection();

        $select = $connection->select()
            ->from(['e' => $this->resource->getTableName('catalog_product_entity')], ['sku'])
            ->join(
                ['p' => $this->resource->getTableName('catalog_product_index_price')],
                'p.entity_id = e.entity_id',
                ['final_price']
            )
            ->where('e.sku IN (?)', $skus)
            ->where('p.customer_group_id = ?', $customerGroupId)
            ->where('p.website_id = ?', $websiteId);

        $prices = [];
        foreach ($connection->fetchAll($select) as $row) {
            $prices[(string) $row['sku']] = (float) $row['final_price'];
        }

        return $prices;
    }
}

// The select above renders as:
// SELECT `e`.`sku`, `p`.`final_price`
// FROM `catalog_product_entity` AS `e`
// INNER JOIN `catalog_product_index_price` AS `p` ON p.entity_id = e.entity_id
// WHERE (e.sku IN ('SKU-1', 'SKU-2')) AND (p.customer_group_id = 0) AND (p.website_id = 1)

When to use it

  • Explain to a hosting provider why the catalog page issues 300 queries.
  • Size innodb_buffer_pool_size for a new database server.
  • Find the one custom query behind a nightly spike in database load.
  • Add a read replica for a browse-heavy storefront.

More examples

What one EAV attribute read costs

The first query is the shape every attribute read takes, repeated per attribute and per store scope, with the store_id ordering there because a store value overrides the default. Multiply it by the attributes a category page shows and by the products on the page and the query count explains the page time on its own. The second query is what an index table buys you. Reach for this comparison when deciding whether a custom listing should read products through a collection or through an index table you control.

Example · sql
-- Reading the name of one product from EAV
SELECT v.value
FROM catalog_product_entity_varchar AS v
INNER JOIN eav_attribute AS a
    ON a.attribute_id = v.attribute_id
   AND a.attribute_code = 'name'
INNER JOIN eav_entity_type AS t
    ON t.entity_type_id = a.entity_type_id
   AND t.entity_type_code = 'catalog_product'
WHERE v.entity_id = 42
  AND v.store_id IN (0, 1)
ORDER BY v.store_id DESC
LIMIT 1;

-- The same value from the flat/index side, when the flat catalog is built
SELECT name FROM catalog_product_flat_1 WHERE entity_id = 42;

Slow query log and the settings worth changing

long_query_time of 0.1 is deliberately aggressive: with the default of 10 seconds a Magento log stays empty while every page is slow. Leave log_queries_not_using_indexes off, because Magento issues many small unindexed lookups on tiny tables and they drown the log. Set innodb_buffer_pool_size to roughly 70 percent of a dedicated database server's memory. tmp_table_size and max_heap_table_size take effect at the lower of the two, which is why changing only one of them appears to do nothing. MySQL must be restarted for buffer pool changes.

Example · ini
; /etc/mysql/conf.d/magento.cnf
[mysqld]
; Find the slow queries
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1
log_queries_not_using_indexes = 0

; The single most important setting: hold the working set in memory
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12

; Catalog queries build big temporary tables; these two must match
tmp_table_size = 256M
max_heap_table_size = 256M

max_connections = 400

Routing reads to a replica

The slave_connection entry must be named after the master connection it shadows, which is why this one is called default. Magento then sends reads for non-write requests to the replica and keeps writes on the primary. The indexer connection is excluded by design, because reindexing writes. Reach for this when the database is the bottleneck and the load is mostly catalog browsing; it does nothing for a checkout-heavy site, because a request that writes uses the primary for its reads too. Replication lag becomes visible as a product that looks stale for a second after a save.

Example · php
<?php
// app/etc/env.php (fragment)
return [
    'db' => [
        'connection' => [
            'default' => [
                'host' => 'mysql-primary.internal',
                'dbname' => 'magento',
                'username' => 'magento',
                'password' => 'secret',
                'active' => '1',
            ],
        ],
        'slave_connection' => [
            'default' => [
                'host' => 'mysql-replica.internal',
                'dbname' => 'magento',
                'username' => 'magento_ro',
                'password' => 'secret',
                'active' => '1',
            ],
        ],
        'table_prefix' => '',
    ],
];

Related

m2 eav queriesm2 eav flatm2 collectionsm2 security sqlm2 perf scale

Discussion

  • Be the first to comment on this lesson.