Magento GraphQL and EAV: What Product Queries Really Cost

1. The EAV Model in Magento: The Basics

The Entity-Attribute-Value model (EAV) is a database architecture that Magento uses for products, customers, and categories. Instead of storing all attributes in one wide table, say catalog_product with a hundred columns, EAV stores each attribute value as its own row in typed value tables. The advantage: an arbitrary number of custom attributes can be added without schema changes. The disadvantage: every attribute you query requires a join across the corresponding value table.

In practice this means: a product list of 20 items, each returning 30 attributes in the GraphQL response, can internally assemble over 600 EAV values from different tables, one separate join per attribute value table (varchar, int, decimal, text, datetime). Magento has mechanisms for this problem, but they only work under certain conditions, and many projects do not use them fully.

2. Which EAV Tables Are Involved in Product Queries

Product EAV data is split across five typed value tables: catalog_product_entity_varchar for text such as name and URL key, catalog_product_entity_int for integer values such as status and visibility, catalog_product_entity_decimal for decimal values such as price and weight, catalog_product_entity_text for long text such as descriptions, and catalog_product_entity_datetime for date fields. On top of that there is catalog_product_entity as the base table holding the immutable identifiers.

Each of these tables has one row per product and attribute, not one row per product. If a product has 40 attributes spread across three value tables, three joins result, each over a table with several million rows in large catalogs. Without suitable indexes and without a materialized flat catalog, these joins are the most common cause of slow product lists in Magento GraphQL setups.

3. How GraphQL Queries Trigger EAV Lookups

A GraphQL query for products triggers a chain of database operations in Magento. The root resolver for products first runs a search or filter query that returns the matching product IDs. For each product ID, the requested attributes are then loaded from the EAV tables. Which attributes get loaded depends on which fields were requested in the GraphQL query, but this connection is not always direct: Magento sometimes loads more attributes internally than the frontend explicitly requested, because certain attributes are always needed for internal processing (price calculation, authorization checks).

That means: even a seemingly lean query with only sku and name can trigger more EAV access internally than expected, if the resolver needs additional attributes for authorization checks or store-specific price information. Without query profiling this overhead is invisible. Only a look at the SQL query log reveals the full picture of the database operations behind a seemingly simple GraphQL query.

# This simple-looking query can trigger many EAV table joins internally
# Magento loads not just name/sku but also status, visibility, price data for authorization
query ProductListEAVTest {
  products(
    filter: { category_id: { eq: "10" } }
    pageSize: 20
  ) {
    total_count
    items {
      sku                    # catalog_product_entity (no JOIN needed)
      name                   # catalog_product_entity_varchar JOIN
      url_key                # catalog_product_entity_varchar JOIN
      status                 # catalog_product_entity_int JOIN
      price_range {
        minimum_price {
          final_price { value }   # complex price calculation (multiple tables)
        }
      }
      special_price          # catalog_product_entity_decimal JOIN
      news_from_date         # catalog_product_entity_datetime JOIN
      description { html }   # catalog_product_entity_text JOIN
    }
  }
}

4. Flat Catalog: The Most Important EAV Countermeasure

The Magento flat catalog is an indexing strategy that merges EAV data into materialized wide tables. Instead of collecting attributes from EAV value tables through five joins when loading a product, catalog_product_flat_1 (for store 1) holds every attribute as its own column, so a simple SELECT is enough. The table is refreshed during indexing (bin/magento indexer:reindex catalog_product_flat) and stays valid until the next reindex.

The flat catalog is not enabled by default and must be turned on in the admin under Stores > Configuration > Catalog > Catalog > Storefront. After activation a full reindex is required once. The gain is substantial: product list queries that generate dozens of joins without a flat catalog collapse into a single table scan of the flat table. For stores with many custom attributes and large catalogs this is often the single biggest performance lever there is.

5. Custom Attributes and Their Impact on Performance

Every custom attribute added to the product entity type through a dedicated module increases EAV complexity. If an attribute is added to the flat catalog, the overhead is minimal, it simply appears as an extra column in the flat table. If an attribute is not in the flat catalog, every access requires an additional join across the EAV value table. Custom modules therefore need to explicitly declare whether their attributes should be included in the flat catalog.

Another problem arises with high-cardinality attributes: attribute sets with many options (for example color with 500 values) create many rows in the catalog_product_entity_int table, and resolving them via eav_attribute_option_value is another join. For GraphQL responses that return attribute labels instead of numeric IDs, these joins multiply further. Resolvers that return attribute labels for filter options should therefore use cached lookup tables instead of joining the option tables on every request.

6. Making EAV Costs Visible: Profiling and Logging

The first step toward EAV optimization is making the actual database cost visible. The Magento SQL query logger shows all SQL queries of a request together with execution times. For a GraphQL product list query you should count how many of the queries hit EAV tables and how much total time they consume. In a poorly configured setup you will often see 70 to 90 percent of request time spent inside EAV table joins.

For deeper analysis, MySQL's EXPLAIN command is the tool of choice for the most frequent queries. A query without a suitable index that scans a million-row EAV table is immediately visible as type: ALL in the EXPLAIN output. For Magento projects it is worthwhile to run a periodic query profiling pass and identify the top ten most expensive SQL queries, in production stores these almost always come from EAV queries or missing indexes.

7. EAV Access Strategies Compared

There are several strategies for reducing EAV overhead. Choosing the right one depends on the size of the catalog, the number of custom attributes, and the requirements around data freshness.

8. Caching Strategies for EAV-Heavy Resolvers

The most effective cache for EAV-heavy resolvers is the Magento response cache, which stores a complete GraphQL response for anonymous queries. On a cache hit, the response is read directly from cache without a single database query taking place, EAV overhead included. The response cache requires a correctly configured identity class that sets cache tags and is enabled by default for public product queries.

For resolvers that return product-related attributes and cannot use a full response cache (for example authenticated queries), a field-specific cache at the service layer is recommended. Product attributes that rarely change, such as name, description, URL key, and images, can be cached in the Magento cache or in Redis. Loading these values from cache is orders of magnitude faster than an EAV join, even for a single product. Cache invalidation happens via the Magento indexers, which are triggered automatically when a product is saved.

9. Indexing Strategy: Which Indexes Actually Help

Magento maintains its own indexes for the EAV tables, but in large installations with many custom attributes and high traffic these can fall short. The most important index for EAV performance queries is a composite index on (entity_id, attribute_id, store_id) in the EAV value tables, this covers the most common query patterns. In MySQL/MariaDB you can use EXPLAIN to check whether existing queries actually use this index.

For the catalog_product_flat index process itself it is important to understand that partial reindexes (only for changed products) are possible and are noticeably faster than full reindexes in production systems. The configuration of mview.xml and the index mode settings determine when and how the flat catalog is refreshed. Real-time reindexing (Update on Save) works well for smaller catalogs; for large stores with frequent price changes a scheduled reindex outside peak hours is recommended.

# Query optimized for Flat-Catalog (all requested fields should be in the flat table)
# Check with: SHOW COLUMNS FROM catalog_product_flat_1
query FlatCatalogOptimizedQuery {
  products(
    filter: { category_id: { eq: "5" } }
    pageSize: 24
    currentPage: 1
  ) {
    total_count
    items {
      # These fields are typically in the flat table: one SELECT, no EAV JOINs
      sku
      name
      url_key
      status
      visibility
      # Price comes from price index, not EAV
      price_range {
        minimum_price {
          final_price { value currency }
          regular_price { value }
        }
      }
      # Image from media gallery index
      small_image { url label }
    }
  }
}

10. Summary

The EAV model is the biggest hidden performance factor in Magento GraphQL product queries. Every attribute you query can trigger one or more joins across EAV value tables, and without a flat catalog this overhead multiplies with the number of products in the result list. Profiling with the SQL query logger makes these costs visible and shows where most of the time is being spent.

The most effective countermeasures are enabling flat catalog and explicitly including custom attributes, configuring response caching for anonymous product lists, and implementing field-specific caching for attributes that rarely change. Combining these measures can cut the load time of a typical product list query by 60 to 90 percent, without having to change the GraphQL interface or the schema design.

11. FAQ: Magento GraphQL and EAV