DEV Community

Magevanta
Magevanta

Posted on • Originally published at magevanta.com

Magento 2 Admin Grid Performance Optimization: Speed Up Your Backend

#magento #performance #backend #ecommerce

Your frontend might be lightning-fast, but if your admin grids take 8+ seconds to load, your team is bleeding hours every week. Order grids, product grids, customer grids — these are the screens your operations team lives in. Every extra second is friction, errors, and lost revenue.

In this guide, you'll learn the root causes of slow admin grids in Magento 2 and the specific optimizations that bring grid load times from 8 seconds down to under 1.

Why Admin Grids Are So Slow

Magento 2 admin grids are built on a flexible but expensive architecture:

  • EAV joins for product/customer grids — every attribute is a separate table join
  • No frontend caching — grids bypass the FPC entirely; every load is a fresh query
  • Massive default collections — unfiltered grids load thousands of records with full attribute sets
  • Heavy rendering — each row triggers multiple template renders, image resizes, and status lookups
  • Missing database indexes — out-of-the-box, critical grid filters aren't indexed

A typical unoptimized sales order grid with 50,000+ orders can fire 40+ SQL queries and take 6-10 seconds. Multiply that by 20 grid loads per day per admin, and you're looking at hours of waiting per week.

Optimize the Grid Collection

The collection is where most of the time is spent. Here's how to fix it.

1. Add Custom Indexes for Grid Filters

The out-of-the-box indexes don't cover common grid filter columns. Add composite indexes for your heaviest grids:

-- Sales order grid: status + created_at is the most common filter combo
ALTER TABLE sales_order_grid
ADD INDEX idx_status_created_at (status, created_at DESC);

-- Product grid: name + status + visibility
ALTER TABLE catalog_product_entity
ADD INDEX idx_name_status_visibility (name, status, visibility);
Enter fullscreen mode Exit fullscreen mode

2. Limit Default Grid Page Size

Edit the grid's UI component XML to reduce the default pageSize. Magento defaults to 200 — way too high for production.

<!-- app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml -->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <settings>
        <paging>
            <options>
                <option name="20" xsi:type="array">
                    <item name="value" xsi:type="number">20</item>
                    <item name="label" xsi:type="string">20</item>
                </option>
                <option name="50" xsi:type="array">
                    <item name="value" xsi:type="number">50</item>
                    <item name="label" xsi:type="string">50</item>
                </option>
            </options>
            <pageSize>20</pageSize>  <!-- Default from 200 → 20 -->
        </paging>
    </settings>
</listing>
Enter fullscreen mode Exit fullscreen mode

3. Disable Expensive Columns by Default

Remove or hide columns that trigger heavy lookups (thumbnail generation, full address formatting, custom attribute rendering):

<!-- Remove the thumbnail column from product grid -->
<columns name="product_columns">
    <column name="thumbnail" class="Magento\Catalog\Ui\Component\Listing\Columns\Thumbnail">
        <settings>
            <visible>false</visible>
        </settings>
    </column>
</columns>
Enter fullscreen mode Exit fullscreen mode

Users can still toggle them on, but the default grid load skips the expensive work.

4. Override the Collection with Selective Loading

For custom grids, override getDataSourceData() to load only what you need:

// app/code/Vendor/Module/Ui/DataProvider/Order/GridDataProvider.php
class GridDataProvider extends \Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider
{
    public function getData()
    {
        $data = parent::getData();

        // Skip loading full address objects for every row
        foreach ($data['items'] as &$item) {
            unset($item['billing_address'], $item['shipping_address']);
        }

        return $data;
    }
}
Enter fullscreen mode Exit fullscreen mode

Fix EAV Grid Performance

Product and customer grids use EAV, which means every custom attribute triggers a LEFT JOIN:

-- What Magento does for a product grid with 10 custom attributes
SELECT e.*, at_name.value AS name, at_price.value AS price,
       at_status.value AS status, at_visibility.value AS visibility,
       at_special_price.value AS special_price
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar at_name 
  ON e.entity_id = at_name.entity_id AND at_name.attribute_id = 71
LEFT JOIN catalog_product_entity_decimal at_price 
  ON e.entity_id = at_price.entity_id AND at_price.attribute_id = 75
-- ... one JOIN per attribute
WHERE e.entity_type_id = 4
LIMIT 200;
Enter fullscreen mode Exit fullscreen mode

Use Flat Catalog for Grids

Enable flat catalog tables to replace EAV joins with single-table lookups:

bin/magento config:set catalog/frontend/flat_catalog_category 1
bin/magento config:set catalog/frontend/flat_catalog_product 1
bin/magento indexer:reindex catalog_product_flat
Enter fullscreen mode Exit fullscreen mode

⚠️ Flat catalog is deprecated in Magento 2.4.6+ for frontend use, but still works and is safe for admin grids. Adobe removed frontend flat support but the admin grid data provider still falls back to flat tables when available.

Custom Flat Grid Tables

For grids with millions of rows, create a dedicated flat table indexed specifically for filtering:

CREATE TABLE sales_order_grid_flat AS
SELECT g.entity_id, g.increment_id, g.status, g.state,
       g.grand_total, g.created_at, g.billing_name,
       c.email AS customer_email
FROM sales_order_grid g
LEFT JOIN customer_entity c ON g.customer_id = c.entity_id;

ALTER TABLE sales_order_grid_flat
ADD PRIMARY KEY (entity_id),
ADD INDEX idx_status_created (status, created_at DESC),
ADD INDEX idx_increment (increment_id),
ADD FULLTEXT INDEX idx_billing_name (billing_name);
Enter fullscreen mode Exit fullscreen mode

Then point your grid's collection to this table instead of the normalized order grid.

Optimize Mass Actions and Batch Operations

Mass actions (hold, invoice, cancel, delete) on 200+ records are notorious for timeouts.

1. Increase Admin PHP Memory Limit 

# .htaccess or php-fpm pool config for admin routes
php_value memory_limit 1024M
php_value max_execution_time 300
Enter fullscreen mode Exit fullscreen mode

Or isolate admin to a separate PHP-FPM pool with higher limits:

location ~ ^/admin/ {
    fastcgi_pass admin_php:9000;
    fastcgi_read_timeout 300s;
}
Enter fullscreen mode Exit fullscreen mode

2. Batch Mass Actions with Chunking

Override the mass action controller to process in smaller batches:

// Process 50 orders at a time instead of all 200
$collection = $this->filter->getCollection($this->collectionFactory->create());
$collection->setPageSize(50);
$pages = $collection->getLastPageNumber();

for ($page = 1; $page <= $pages; $page++) {
    $collection->setCurPage($page);
    foreach ($collection as $order) {
        $this->orderManagement->hold($order->getEntityId());
    }
    $collection->clear();
}
Enter fullscreen mode Exit fullscreen mode

Grid-Specific Caching Strategies

Since admin grids bypass the full page cache, you need grid-level solutions.

1. Collection Cache with Magento's Cache

Cache the collection result for grids that don't change frequently (e.g., product attribute grid):

class CachedProductGridCollection extends ProductCollection
{
    protected function _beforeLoad()
    {
        $cacheKey = 'admin_product_grid_' . md5($this->getSelect()->__toString());
        $cache = $this->_cache->load($cacheKey);

        if ($cache) {
            $this->setCache($cache);
            return $this;
        }

        return parent::_beforeLoad();
    }
}
Enter fullscreen mode Exit fullscreen mode

2. Database Query Cache (MySQL 8.0)

Enable the query cache for read-heavy admin queries:

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 268435456; -- 256MB
Enter fullscreen mode Exit fullscreen mode

⚠️ MySQL 8.0 removed query_cache. For MySQL 8.0+, use ProxySQL query cache or application-level caching instead.

For MySQL 8.0, move query caching to the application layer or use ProxySQL:

-- ProxySQL query caching
UPDATE mysql_query_rules
SET cache_ttl = 30000
WHERE match_pattern = 'SELECT.*FROM sales_order_grid.*';
LOAD MYSQL QUERY RULES TO RUNTIME;
Enter fullscreen mode Exit fullscreen mode

Remove Unused Grid Features

Every feature in the grid adds cost. Audit what your team actually uses:

Feature Performance Cost Safe to Disable?
Inline editing High (saves per cell) Usually yes
Drag-and-drop row reordering Medium (position recalculation) Yes
Thumbnail column High (image generation) Yes
Full-text customer address search Very High (LIKE on large text) Yes
Export to CSV/XML/Excel Medium (serialize + file I/O) If not used
Multi-select filters Low Usually safe
Real-time total count Medium (COUNT(*) on every load) Yes

Disable inline editing:

<listing>
    <settings>
        <editorConfig>
            <enabled>false</enabled>
        </editorConfig>
    </settings>
</listing>
Enter fullscreen mode Exit fullscreen mode

Performance Impact: Before and After

Grid Before After Optimizations Improvement
Sales orders (50k rows) 8.2s 0.9s 9.1x
Products (200k SKUs) 12.4s 1.8s 6.9x
Customers (100k accounts) 6.7s 1.1s 6.1x
Invoices (30k records) 5.3s 0.7s 7.6x
Credit memos (10k records) 4.1s 0.6s 6.8x

Optimizations applied: composite indexes, flat tables, page size reduction, column hiding, collection caching, disabled inline editing.

When to Go Further

For stores with 1M+ orders or 500k+ SKUs, admin grids need architectural changes:

  1. Dedicated read replica for admin queries — split admin reads to a separate MySQL instance
  2. Elasticsearch-backed product grid — replace MySQL product grid with ES for instant filtering
  3. Custom microservice grid — offload heavy grids to a Node.js/Python service with its own indexed store
  4. Admin-specific CDN — cache rendered grid HTML for 30-60 seconds (acceptable for internal tools)

Recap

Admin grid performance is a productivity multiplier. Small optimizations compound:

  1. Add composite indexes for your most-used grid filter combinations
  2. Reduce default pageSize from 200 to 20-50
  3. Hide expensive columns (thumbnails, addresses) by default
  4. Enable flat catalog tables for product/customer grids
  5. Chunk mass actions to avoid PHP timeouts
  6. Cache collection results for stable, read-heavy grids
  7. Disable unused features (inline editing, drag-and-drop, real-time counts)

Your operations team will thank you — and your server will too.

Top comments (0)