Magento stores product data in an entity-attribute-value model, which is why you can add any attribute to any product without a schema change, and also why a large catalog slows down. Every attribute you flag as searchable, filterable, or used in listing adds query work to category and product pages. Attribute set design controls that cost.
Most performance advice for large catalogs jumps straight to caching and search engines. Those matter, but they treat the symptom. The root cause on a catalog with tens or hundreds of thousands of SKUs is usually attribute bloat: too many attributes, too many of them flagged for jobs they do not need, spread across too few attribute sets. This is the layer we audit first on Magento and Adobe Commerce development engagements, because fixing it makes every later optimization cheaper.
Why EAV is flexible and slow at the same time
In a flat relational design, a product is one row and each attribute is a column. Magento does not do that. Instead it spreads a product’s values across a set of typed value tables, so a single product with dozens of attributes lives in many rows across several tables.
The base table, catalog_product_entity, holds only the fixed columns: entity ID, SKU, attribute set, type ID, and a few others. Everything else lives in value tables named by data type, following a consistent convention. As scandiweb’s explainer of the EAV model puts it, the data-type suffix tells you the column format and the prefix tells you the entity:
| Value table | Holds | Example attributes |
|---|---|---|
catalog_product_entity_varchar |
Short strings | name, url_key, meta_title |
catalog_product_entity_text |
Long strings | description, short_description |
catalog_product_entity_int |
Integers and option IDs | status, visibility, dropdown selects |
catalog_product_entity_decimal |
Numbers | price, weight, custom decimals |
catalog_product_entity_datetime |
Dates | special_from_date, custom dates |
To assemble one product, Magento joins the base table to each value table it needs, filtered on attribute ID and store scope. Loading a product with dozens of attributes takes many JOINs across these vertical tables, and a category page rendering a grid of products multiplies that by every product and every attribute the listing touches. The flexibility that lets a merchandiser add a new spec field in the admin is the same design that turns a busy category page into a large pile of small queries. Filtering by an attribute at scale is slow in SQL, which is exactly why Magento offloads search and faceting to a search engine rather than asking MySQL to do it.
How attribute flags multiply the query cost
The number of attributes is only half the story. What each attribute is flagged to do is the other half, and it is where teams accidentally create most of the load. Every property you enable in the attribute’s storefront properties adds it to an index, a query, or both.
| Attribute property | What it does | Cost on a large catalog |
|---|---|---|
| Used in Product Listing | Loads the value on category and search grids | Adds the attribute to every product in every listing query |
| Used for Sorting in Product Listing | Allows sort by this attribute | Extra index columns and sort overhead |
| Filterable (in layered navigation) | Shows the attribute as a facet | Adds it to the search index and faceting |
| Filterable in Search | Facet on search results | More index weight |
| Comparable / Used in Compare | Appears in product compare | Extra value loads |
| Searchable | Included in fulltext search | Larger search index, slower reindex |
None of these are wrong on their own. The problem is defaults and drift. Attributes get created for a one-off need, flagged filterable “just in case,” and never revisited. On a catalog with 100 attributes, if 60 are flagged used-in-listing or filterable, every category page and every reindex carries all 60 whether shoppers use them or not. The discipline is simple to state and hard to maintain: an attribute earns a storefront flag only when a real merchandising or search requirement needs it. Auditing and removing unused flags is usually the fastest structural win on a slow large catalog, and it costs nothing but review time.
Attribute set design: fewer, tighter sets
An attribute set is a template that defines which attributes a product has. Magento ships with a Default set, and the easy mistake is to pile every attribute the business has ever wanted into it, so that every product carries every attribute even when 80 percent are empty.
For a large catalog the better pattern is a small number of purpose-built sets that match how products actually differ. A store selling apparel, electronics, and consumables does not need one giant set with color, size, screen resolution, and expiry date all attached to every SKU. It needs three lean sets, each carrying only the attributes its products use, sharing common groups for the fields every product needs.
A few rules we apply when designing sets for scale:
- Start from a minimal base. Keep the shared attributes (name, price, description, images, SEO fields) in a common group and inherit them, rather than duplicating.
- Model sets on product families, not departments. The question is which attributes a product type genuinely has, not which team owns it.
- Do not flag at the set level what only one set needs. A filterable attribute attached to a set where it is always empty still costs index weight.
- Retire attributes, not just hide them. An attribute left on a set but unused still loads. Removing it from the set is what recovers the query.
Where product data is sprawling enough that attribute governance becomes its own job, the answer is often to manage it upstream in a product information management system and sync clean, structured data into Magento. Our guide to integrating a PIM with Magento and Hyva covers when Akeneo or inRiver earns its place.
The indexers that carry catalog performance
Magento does not run those EAV joins live on every page. It precomputes them into index tables, and the health of those indexers is what separates a fast large catalog from a slow one. Per Adobe’s indexer management documentation, the catalog-related indexers include:
| Indexer code | Name | Job |
|---|---|---|
catalog_product_price |
Product Price | Precomputes prices including rules and tiers |
catalog_product_attribute |
Product EAV | Flattens attribute values used in listings |
catalog_category_product |
Category Products | Maps which products belong to which category |
catalog_product_category |
Product Categories | The reverse mapping |
catalogsearch_fulltext |
Catalog Search | Builds the search and faceting index |
The single most important setting is the indexing mode. Update on Save reindexes immediately when an admin change is made, which sounds convenient but stalls under bulk imports and heavy edits. Update by Schedule, which uses materialized views to track only what changed, is the production default for any catalog of size. Getting cron and the indexers healthy is foundational, and it is a topic in itself; see our guide to Adobe Commerce cron and indexer health.
The Catalog Search indexer deserves special mention. Since Magento 2.4 a search engine has been mandatory, and as of 2.4.8 OpenSearch is the recommended engine with Elasticsearch deprecated. Faceted navigation on a large catalog belongs in that engine, not in MySQL, because it handles attribute filtering at scale far better than EAV joins can. If your layered navigation is slow, the fix is usually in the search tier, which we cover in OpenSearch vs Elasticsearch for Adobe Commerce and in layered navigation performance on Hyva.
Why flat catalog is not the answer anymore
For years the standard advice was to enable the flat catalog, which collapses the EAV value tables into one wide table per product and per category. It made sense on Magento 1 and early Magento 2. It does not make sense now.
Flat catalog is deprecated in current Magento, and mgt-commerce’s flat catalog guide lays out why it can hurt more than it helps: it adds a second indexing process that can conflict with the EAV indexers and produce stale or inaccurate data, it breaks with third-party extensions that expect EAV, and a wide flat table with hundreds of attributes runs into MySQL column limits and NULL bloat where most columns are empty. The modern architecture keeps EAV for storage, precomputes the heavy lifting into the standard indexers, and pushes search and faceting to OpenSearch. That combination is faster and more stable than flat catalog on a large store, without the sync fragility.
How attributes render on a Hyva storefront
Attribute design is a backend concern, but it surfaces on the frontend, and Hyva changes how. On a legacy Luma theme, attribute-heavy product and listing pages carry the weight of Knockout, RequireJS, and jQuery on top of the EAV load. Hyva strips that frontend stack down to inline Alpine.js and Tailwind, so the storefront renders the same attribute data with far less JavaScript. That is why we build storefronts on Hyva for Hyva development projects.
The interaction to watch is layered navigation and product listings, where a large catalog with many filterable attributes meets the frontend. Hyva renders facets efficiently, but it can only render what the backend gives it: if the search index carries 60 filterable attributes because nobody pruned them, the facet list is long and the query is heavy no matter how light the theme is. Complex product types add another layer, since configurable and bundle products fan out into their child attributes; our guide to rendering complex catalogs on Hyva covers how swatches and options behave. The rule holds end to end: clean attribute design upstream makes the storefront faster downstream.
A practical attribute-hygiene checklist
When we audit a large catalog, this is the short list we run:
- Count attributes and flags. Export the attribute list with its storefront properties. Anything flagged filterable, searchable, or used-in-listing is a candidate for review.
- Match flags to real usage. A filterable attribute with no facet clicks and a used-in-listing attribute that never appears in the grid are pure cost.
- Consolidate attribute sets. Look for one bloated set doing the job of several lean ones.
- Confirm indexing mode. Update by Schedule, not on save, for anything at scale.
- Move faceting to OpenSearch and tune it, rather than leaning on MySQL.
- Watch the database size. Unused attributes and stale index data contribute to bloat; see Magento database bloat and cleanup.
Where other platforms sit, and where we fit
Magento’s EAV model is unusually flexible, which is both its strength for complex B2B and large catalogs and the reason it needs governance. Other platforms make different trade-offs. Shopify models extra product data as metafields with a simpler but more constrained structure; Shopware and BigCommerce each have their own attribute and custom-field systems. None of them removes the underlying truth that a large, attribute-rich catalog needs deliberate data design to stay fast.
For merchants running Magento or Adobe Commerce at scale, attribute and EAV design is one of the highest-value places to spend engineering time, because it compounds: cleaner attributes mean lighter indexes, faster faceting, and a quicker storefront. If you want context on the team, about Bemeir covers our background, our technology partner ecosystem covers the search and PIM tools this work touches, and Bemeir is where it starts.
Frequently asked questions
Does reducing the number of attributes really speed up Magento?
Yes, but the flags matter more than the raw count. An attribute that is stored but not flagged searchable, filterable, or used-in-listing adds little runtime cost. The expensive attributes are the ones pulled into listing queries and the search index. Prune the flags first, then retire genuinely unused attributes from their sets.
Should I enable flat catalog on a large store?
No. Flat catalog is deprecated and can conflict with the standard indexers, break extensions, and hit MySQL column limits on wide catalogs. Keep EAV for storage, run the standard indexers on schedule, and push faceting to OpenSearch. That is faster and more stable on a large catalog than flat tables.
How many attribute sets should a large catalog have?
There is no fixed number. Model sets on product families so each set carries only the attributes its products actually use, and share common fields through inherited groups. A handful of lean, purpose-built sets almost always outperforms one giant Default set that attaches every attribute to every product.
Why is my layered navigation slow even on Hyva?
Because layered navigation depends on the search index and the number of filterable attributes, not on the theme. Hyva renders facets efficiently, but if 60 attributes are flagged filterable, the facet list and the underlying query are heavy regardless. Prune filterable flags and tune OpenSearch, and the storefront follows.
What indexing mode should a production Magento store use?
Update by Schedule. It uses materialized views to reindex only what changed, which keeps the site responsive during bulk imports and edits. Update on Save reindexes immediately on every change and stalls under load, so it is only appropriate for small catalogs or development environments.
