Magento 2 Image Resizing Bottleneck: A Critical Database Indexing Fix

Unmasking a Hidden Performance Drain in Magento 2 Image Resizing

As e-commerce migration experts at Shopping Mover, we often encounter intricate performance challenges within Magento 2. A recent GitHub issue (magento/magento2#41567) sheds light on a significant, yet often overlooked, database bottleneck affecting image resizing on demand. This issue, reported on Magento 2.4.x, reveals how a missing database index can lead to severe performance degradation, particularly for stores with large product catalogs.

The Core Problem: Full Catalogue Scans for Image Resizing

The problem stems from Magento's get.php script, responsible for generating resized product images on the fly. When an image cache misses, the system calls Magento\Catalog\Model\ResourceModel\Product\Image::getRelatedWebsiteIds($filepath). This function executes a complex SQL query to determine which websites are associated with a given image:

SELECT DISTINCTROW images.value AS filepath, GROUP_CONCAT(websites.website_id SEPARATOR ',') AS website_ids
FROM catalog_product_entity_media_gallery AS images
INNER JOIN catalog_product_entity_media_gallery_value AS image_value ON images.value_id = image_value.value_id
INNER JOIN catalog_product_entity AS products ON image_value.entity_id = products.entity_id
INNER JOIN catalog_product_website AS websites ON products.entity_id = websites.product_id
WHERE (images.disabled = 0 AND image_value.disabled = 0) AND (images.value = ?)
GROUP BY websites.product_id

The critical flaw lies in the catalog_product_entity_media_gallery table. Despite filtering by images.value, this column lacks a database index. Consequently, the database optimizer is forced to perform a full table scan, starting from catalog_product_website, resulting in inefficient queries and significant server load.

Real-World Impact and Benchmarks

The issue author provided compelling evidence from a production environment with ~48,000 products and nearly 190,000 rows in catalog_product_entity_media_gallery:

• Average Query Time: 290 ms (up to 927 ms max)
• Rows Examined: ~519,000 per call
• Server Time: 17.7% of all statement time on the server

This directly translates to slow image loading for customers and search engine bots (like Googlebot-Image), impacting user experience and potentially SEO. The cost of this inefficiency grows proportionally with the size of the product catalog.

The Simple, Yet Powerful, Solution

The fix is remarkably straightforward: add an index to the value column of the catalog_product_entity_media_gallery table. Benchmarking showed a dramatic improvement:

• Without Index (p50): 254.8 ms
• With INDEX(value) (p50): 0.7 ms

That's a performance gain of over 360 times! In production, the average query time dropped to 1.2 ms. This index is small (~10 MB for 189k rows) and can be built online without downtime. The proposed solution involves adding the following to Magento_Catalog's db_schema.xml:

This fix not only resolves the image resizing bottleneck but also benefits any other queries that filter this table on the value column, such as duplicate image checks or import processes.

Conclusion for Magento Merchants and Developers

This issue highlights the critical importance of proper database indexing in Magento 2. Even a seemingly minor oversight can lead to substantial performance issues, especially as your store scales. For Magento users and developers, understanding and implementing such optimizations is key to maintaining a fast, responsive, and efficient e-commerce platform. While a pull request is underway to include this fix in future Magento versions, affected stores can implement this index as a custom module workaround immediately to see significant performance gains.