Handy MySQL queries and views for Magento 2

2021-11-10 · Ben Williamson

Why I keep named queries

I spent years collecting one-off SQL in Navicat folders. Useful ones graduate to named views or documented snippets with a one-line note about when to run them. That beats re-deriving the same EAV joins after every environment down-sync.

My rule of thumb: if I run it twice in a month, or if it saved me from a bad assumption once, it earns a saved name. Everything else stays in the scratch folder until it proves itself.

Enterprise vs Community column names

Most of my production queries target Magento Commerce (EE). My Windows local stack is often Open Source (CE). The same logical join can break when you copy SQL between them:

  • row_id vs entity_id. EE varchar/int/datetime tables often join on row_id. CE uses entity_id on the same tables.

  • Attribute backend types. The same attribute code can land in catalog_product_entity_varchar on one install and catalog_product_entity_text on another depending on version and migration history.

I annotate saved files when a query needs an EE→CE swap so future-me does not paste blindly.

Product url_key lookup

The classic “give me SKU and url_key for every product” query is a good template for how EAV joins repeat across Magento ops work:

SELECT cpe.sku, cpev.value AS url_key
FROM catalog_product_entity cpe
LEFT JOIN catalog_product_entity_varchar cpev
  ON cpev.entity_id = cpe.entity_id
  AND cpev.store_id = 0
LEFT JOIN eav_attribute ea
  ON ea.attribute_id = cpev.attribute_id
  AND ea.attribute_code = 'url_key'
LEFT JOIN eav_entity_type eet
  ON eet.entity_type_id = ea.entity_type_id
  AND eet.entity_type_code = 'catalog_product';

On Commerce, swap the join to cpev.row_id = cpe.row_id when your schema uses staged rows. Scope store_id deliberately: global (0) vs store-view overrides behave differently on multi-store catalogs.

Required product attributes

When writing product import or creation code, I often need the current required attribute set, not the admin form labels:

SELECT ea.attribute_code, ea.backend_type, ea.is_required
FROM eav_attribute ea
WHERE ea.is_required = 1
  AND ea.entity_type_id = (
    SELECT eet.entity_type_id
    FROM eav_entity_type eet
    WHERE eet.entity_type_code = 'catalog_product'
  )
ORDER BY ea.attribute_code;

Pair this with your import validator so missing required EAV fields fail early instead of halfway through a bulk save.

Catalog visibility on a store view

“Missing PLP rows” often traces to visibility scoped wrong on a single store view, not search or indexers:

SELECT e.sku, s.store_id, s.value AS visibility
FROM catalog_product_entity e
JOIN catalog_product_entity_int s
  ON s.entity_id = e.entity_id
  AND s.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE attribute_code = 'visibility' AND entity_type_id = 4
  )
WHERE s.value = 1
ORDER BY e.sku, s.store_id;

Visibility 1 is “Not Visible Individually.” Fix the scope flag before you flush indexers or resync search feeds.

Indexer state before blind flushes

When changelog tables balloon, inspect stuck rows before running a blanket reindex:

SELECT indexer_id, status, updated
FROM indexer_state
ORDER BY updated DESC;

If one indexer sits in working for hours, fix that first. A full reindex on top of a stuck worker usually wastes time.

Config scope debugging

Search and catalog config issues often trace to a single store-view row in core_config_data, not a global default:

SELECT path, scope, scope_id, value
FROM core_config_data
WHERE path LIKE 'catalog/search%'
ORDER BY scope, scope_id;

Document with diagrams

Each durable query earns a small flow: symptom → tables → decision. Future-you will not remember why a particular join was safe six months later.

What stays internal

Publish patterns, not secrets. No customer SKUs, production hostnames, or credential-bearing connection strings in public snippets.