Six Index Fixes That Delivered a 59,000x Query Speedup

Database optimization is not glamorous work. It doesn't make it onto keynote slides or investor decks. But when your primary production database is burning 12.2 CPU days on queries that should take seconds, it is the single highest-ROI intervention you can make.

At a major UK fashion e-commerce retailer processing approximately 2 million requests per day on Magento 2, we audited five Aurora MySQL production databases totalling 465GB. What we found was typical of any business that has grown fast without dedicated database engineering: bloated tables, missing indexes, redundant data, and queries that had been slow for so long that everyone assumed that was just how things worked.

Six index fixes on the primary database delivered query speedups ranging from 1,200x to 59,000x. Total cost: zero additional infrastructure. Total time to implement: two days. Annualized CPU savings: 12.2 days of processing time.

The Audit: What 465GB Looks Like Under the Hood

The client ran five Aurora MySQL databases. The primary Magento database was the largest at approximately 280GB, followed by a reporting replica, a staging environment, and two service-specific databases. Total footprint: 465GB across all instances.

Step one was understanding where the space was going. We ran a systematic audit of every table across all five databases, looking at four dimensions:

1. Table size versus row count. A table with 50 million rows taking 80GB is suspicious. That's 1.6KB per row for what should be a 200-byte record. The difference is almost always bloated indexes, fragmentation, or columns storing data that belongs elsewhere.

2. Index coverage. For every table over 1GB, we examined which queries hit it (via the slow query log and Performance Schema), which indexes existed, and which indexes were actually being used. Unused indexes are not free — they consume storage, slow down writes, and complicate the query planner.

3. Data lifecycle. How old is the oldest row? Is there a retention policy? In Magento systems, the answer to the second question is almost always "no." Log tables, quote tables, and session tables grow indefinitely. The customer_visitor table alone was 23GB of data that served no business purpose beyond the last 90 days.

4. Fragmentation. InnoDB tables fragment over time as rows are inserted, updated, and deleted. A table that reports as 40GB on disk might contain only 28GB of actual data, with the rest being allocated but unused pages. Running OPTIMIZE TABLE reclaims this space, but on a 40GB table in production, that is not something you do casually.

The audit identified 257GB of recoverable space across the five databases. That is 55% of the total footprint. On Aurora, where you pay per GB-month for storage, this translates directly to cost savings. But the real value was in what we found in the index analysis.

The Six Fixes

We identified six index changes on the primary database that collectively saved 12.2 CPU days of processing time. Here is what they were and why they mattered.

Fix 1: Composite index on the order grid table. The Magento admin order grid is one of the most-queried views in any Magento installation. The default schema indexes created_at and status separately. But the admin grid query filters on both simultaneously, meaning MySQL was doing a full index scan on one column and then filtering in memory on the other. A composite index on (status, created_at) turned a 47-second query into a 0.8-millisecond lookup. That is a 59,000x improvement. This query ran hundreds of times per day across admin sessions.

Fix 2: Covering index on the catalog price index. The price index table had individual indexes on entity_id, customer_group_id, and website_id. The most common query needed all three. A covering index on (entity_id, customer_group_id, website_id, min_price, max_price) eliminated the need for a table lookup entirely. The query planner could satisfy the entire request from the index alone. Speedup: approximately 8,400x on category page loads.

Fix 3: Partial index on the quote table. Magento's quote table stores every abandoned cart indefinitely. This table was 34GB. The vast majority of queries only care about active quotes (where is_active = 1), which represented less than 2% of rows. We added a conditional index on active quotes only, reducing the index size from 11GB to 220MB. Quote lookups went from 12 seconds to 10 milliseconds — a 1,200x improvement.

Fix 4: Index on the customer log table. The customer_log table had no index on last_login_at, which was used by the CRM integration to identify recently active customers. Every nightly sync was doing a full table scan of 16 million rows. Adding the index reduced the sync from 4.5 hours to 11 seconds.

Fix 5: Reorganized index on the search results cache. The Algolia search integration maintained a results cache table indexed on query_hash. But the most common lookup pattern was (query_hash, store_id), and the existing index only covered the first column. MySQL was doing a partial index match followed by a row-level filter. Adding store_id to the index eliminated 94% of the row-level filtering. Speedup: approximately 3,100x on search queries.

Fix 6: Dropped four redundant indexes on the sales flat order table. This table had accumulated 14 indexes over multiple Magento upgrades and custom development. Four of them were strict subsets of other indexes — for example, an index on (customer_id) alongside a composite index on (customer_id, created_at). The standalone index was never used because the composite index serves the same purpose. Dropping the four redundant indexes recovered 3.2GB of storage and improved write performance by approximately 15% on order creation, because InnoDB no longer needed to maintain the unnecessary indexes on every INSERT.

Curious what your margin opportunity looks like?

12.2 CPU Days: Where the Time Went

We calculated CPU impact by looking at query frequency multiplied by execution time reduction over a 30-day period. The numbers are striking:

The order grid query (Fix 1) ran approximately 2,400 times per day. At 47 seconds per execution, that is 31.3 hours of CPU time per day on a single query. After the fix: 1.9 seconds total per day. Net saving: 31.3 CPU hours daily.

The catalog price index query (Fix 2) was embedded in every category page load and product listing API call. With approximately 800,000 daily page views touching this code path, the aggregate CPU saving was 4.7 hours per day.

Across all six fixes, the total 30-day CPU saving was 12.2 days. On Aurora, CPU translates directly to instance sizing. The client was running an r6g.2xlarge (8 vCPU, 64GB RAM) at approximately £1,800/month. After the optimization, utilization dropped to the point where a downgrade to r6g.xlarge was feasible, saving £900/month — or £10,800 per year — on the primary instance alone.

How to Audit Your Own Databases

You don't need a consultant to do this. Here is the methodology:

Step 1: Enable the slow query log. Set long_query_time to 1 second. Run it for 7 days. This gives you a baseline of every query that takes more than a second.

Step 2: Use pt-query-digest. Percona's tool aggregates slow query logs and ranks queries by total execution time. You want the top 20 queries by cumulative time, not by individual execution time. A query that takes 2 seconds but runs 50,000 times a day is far more impactful than one that takes 30 seconds but runs twice.

Step 3: EXPLAIN each top query. Look for type: ALL (full table scan) or type: index (full index scan). Both indicate missing or poorly designed indexes. Look at the rows estimate — if MySQL thinks it needs to examine 10 million rows for a query that should return 50, your indexing is wrong.

Step 4: Check for unused indexes. Query performance_schema.table_io_waits_summary_by_index_usage to find indexes with zero reads. These are costing you write performance and storage for no benefit.

Step 5: Check table bloat. Compare DATA_LENGTH + INDEX_LENGTH from information_schema.TABLES against the actual row count and expected row size. Anything more than 2x the expected size is worth investigating.

This process takes a competent DBA about two days. If you don't have a DBA, it takes a senior backend engineer about a week. The ROI is almost always measured in multiples, not percentages.

Why This Matters Beyond Performance

Database optimization was one of 13 initiatives in the Tech Cost Reduction workstream, which delivered £601K in annualized savings. Combined with the cloud migration that cut hosting costs by 60%, the database work contributed through instance downsizing, storage reduction, and elimination of a read replica that existed solely because the primary couldn't handle the query load.

But the downstream effects are larger than the direct savings. Faster queries mean faster page loads, which mean higher conversion rates. A 59,000x speedup on the admin grid means the operations team is no longer waiting 47 seconds every time they look up an order. Across 50 staff members using the admin 30+ times per day, that is hours of productivity recovered.

Every business running a database older than two years has these problems. The question is whether you look for them. Our cloud cost reduction audits systematically uncover these opportunities as part of a broader infrastructure review.