devLap

Magento 2 Quote Table Optimization: Scaling Checkout with Millions of Abandoned Carts

php dev.to

Every Magento 2 store has a silent performance killer hiding in its database: the quote and quote_item tables. Every time a customer adds a product to their cart — whether they complete checkout or abandon it — Magento creates a quote record. Over months and years, these tables grow to millions of rows, and nobody notices until checkout starts timing out.

I've seen stores where quote had 15 million rows and quote_item had 40 million. Checkout page load went from 1.2 seconds to 8 seconds. The cart page took longer than the product page. And the merchant had no idea why — because the frontend was cached, the database looked fine on paper, and the slow query log wasn't catching the right queries.

In this post, I'll show you exactly how to diagnose quote table bloat, clean it up safely, and prevent it from coming back.

Why Quote Tables Are a Problem

The quote table is central to Magento's checkout flow. Every cart — active or abandoned — is a quote. The quote_item table stores each line item. When a customer proceeds to checkout, Magento loads the quote, joins it with quote items, calculates totals, checks inventory, applies rules, and renders the cart/checkout page.

Here's what happens when these tables grow unbounded:

  1. Cart page load increases — Loading a quote with 20 items requires joining quote, quote_item, quote_item_option, quote_address, quote_address_item, quote_payment, and quote_shipping_rate. Every one of these tables is filtered by quote_id, and if the quote table is 10M+ rows, even indexed queries slow down due to buffer pool pressure.
  2. Checkout AJAX calls slow down — Totals calculation, shipping estimation, and payment method evaluation all query the quote tables. These are uncached backend calls.
  3. Admin order creation slows down — When a merchant creates an order from the admin, Magento queries the quote tables to find existing customer carts.
  4. Database backup and recovery times increase — A 15M row quote table adds significant time to mysqldump and point-in-time recovery.
  5. Memory pressure on PHP-FPM — Magento loads quote data into PHP objects. With a bloated quote_item table, the collection loading consumes more memory per request.

Step 1: Diagnose the Problem

Before fixing anything, measure the current state:

-- Check row counts
SELECT 
  (SELECT COUNT(*) FROM quote) AS quote_count,
  (SELECT COUNT(*) FROM quote_item) AS quote_item_count,
  (SELECT COUNT(*) FROM quote_address) AS quote_address_count,
  (SELECT COUNT(*) FROM quote_item_option) AS quote_item_option_count;

-- Check table sizes
SELECT 
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name LIKE 'quote%'
ORDER BY data_length DESC;

-- Check active vs abandoned quotes
SELECT 
  COUNT(*) AS total,
  SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active,
  SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) AS inactive,
  SUM(CASE WHEN is_active = 0 AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS stale_inactive
FROM quote;
Enter fullscreen mode Exit fullscreen mode

The stale_inactive count is your problem. These are abandoned carts older than 30 days that serve no purpose but still occupy space and slow down queries.

Also check for slow queries related to quotes:

SELECT * FROM mysql.slow_log
WHERE sql_text LIKE '%quote%'
ORDER BY query_time DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Or if you're using Percona or have the performance schema enabled:

SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%quote%'
ORDER BY avg_timer_wait DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Step 2: Clean Up Abandoned Quotes

Magento has a built-in mechanism for this, but it's conservative and often disabled. Here's how to do it properly.

The Safe Approach: Archive First

Before deleting anything, archive old quotes to a separate table:

-- Create archive tables
CREATE TABLE quote_archive LIKE quote;
CREATE TABLE quote_item_archive LIKE quote_item;
CREATE TABLE quote_address_archive LIKE quote_address;
CREATE TABLE quote_item_option_archive LIKE quote_item_option;

-- Move inactive quotes older than 90 days
INSERT INTO quote_archive 
SELECT * FROM quote 
WHERE is_active = 0 
AND updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

INSERT INTO quote_item_archive
SELECT qi.* FROM quote_item qi
INNER JOIN quote_archive qa ON qi.quote_id = qa.entity_id;

INSERT INTO quote_address_archive
SELECT qa.* FROM quote_address qa
INNER JOIN quote_archive q ON qa.quote_id = q.entity_id;

INSERT INTO quote_item_option_archive
SELECT qio.* FROM quote_item_option qio
INNER JOIN quote_item_archive qia ON qio.item_id = qia.item_id;
Enter fullscreen mode Exit fullscreen mode

Verify the archive counts match:

SELECT 
  (SELECT COUNT(*) FROM quote_archive) AS archived_quotes,
  (SELECT COUNT(*) FROM quote_item_archive) AS archived_items;
Enter fullscreen mode Exit fullscreen mode

Delete in Batches (Never DELETE All at Once)

Deleting millions of rows in one transaction will lock the table and potentially crash MySQL. Use batched deletes:

-- Delete in batches of 10,000
SET @batch_size = 10000;
SET @continue = 1;

WHILE @continue > 0 DO
  DELETE FROM quote_item_option 
  WHERE item_id IN (
    SELECT qio.item_id 
    FROM quote_item_option qio
    INNER JOIN quote_item qi ON qio.item_id = qi.item_id
    INNER JOIN quote q ON qi.quote_id = q.entity_id
    WHERE q.is_active = 0 
    AND q.updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
    LIMIT @batch_size
  );

  SET @continue = ROW_COUNT();

  -- Sleep to reduce replication lag
  DO SLEEP(0.5);
END WHILE;
Enter fullscreen mode Exit fullscreen mode

Repeat this pattern for quote_address_item, quote_shipping_rate, quote_payment, quote_address, quote_item, and finally quote — in that order, to respect foreign key relationships.

If your tables use InnoDB with foreign key constraints (they should), you may need to temporarily disable constraint checking or delete child tables first.

Use Magento's Built-in Sales Cleaning

Magento includes a CLI command for cleaning up expired quotes:

bin/magento sales:cleanQuotes
Enter fullscreen mode Exit fullscreen mode

By default, this removes quotes older than the quote/lifetime configuration value (typically 30 days). However, it only removes quotes that have no related orders. You can configure the lifetime in:

Stores → Configuration → Sales → Sales → Order Cleaning

Set "Quote Lifetime" to 30 or 60 days. But be aware — this command can be slow on very large tables and may timeout if run during peak hours.

Step 3: Optimize the Remaining Quotes

After cleanup, optimize the table to reclaim space:

OPTIMIZE TABLE quote;
OPTIMIZE TABLE quote_item;
OPTIMIZE TABLE quote_address;
OPTIMIZE TABLE quote_item_option;
Enter fullscreen mode Exit fullscreen mode

On a 15M row table, this can take 5–15 minutes. Run it during a maintenance window.

Add Missing Indexes

Magento's default schema includes indexes on quote_id, customer_id, and is_active. But depending on your MySQL version and table size, you may benefit from composite indexes:

-- Composite index for the most common admin query
ALTER TABLE quote ADD INDEX idx_active_updated (is_active, updated_at);

-- Covering index for cart loading
ALTER TABLE quote_item ADD INDEX idx_quote_id_product_id (quote_id, product_id);
Enter fullscreen mode Exit fullscreen mode

Always test new indexes on a staging environment first. Use EXPLAIN to verify the index is being used:

EXPLAIN SELECT * FROM quote WHERE is_active = 0 AND updated_at < '2026-04-15';
Enter fullscreen mode Exit fullscreen mode

Step 4: Prevent Future Bloat

Cleaning up once isn't enough. You need a strategy to keep quote tables small going forward.

Configure a Cron Job for Regular Cleanup

Create a cron job that runs the cleanup weekly during low-traffic hours:

# crontab -e
0 3 * * 0 /usr/bin/php /var/www/magento/bin/magento sales:cleanQuotes >> /var/log/magento/quote-cleanup.log 2>&1
Enter fullscreen mode Exit fullscreen mode

Set Up a Custom Cleanup Script

If Magento's built-in cleanup isn't aggressive enough, create a custom script:

<?php
// cleanup-quotes.php
use Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$resource = $objectManager->get(\Magento\Framework\App\ResourceConnection::class);
$connection = $resource->getConnection();

$batchSize = 5000;
$threshold = date('Y-m-d H:i:s', strtotime('-60 days'));

// Get stale inactive quote IDs
$quoteIds = $connection->fetchCol(
    "SELECT entity_id FROM quote 
     WHERE is_active = 0 
     AND updated_at < ? 
     AND entity_id NOT IN (SELECT quote_id FROM sales_order) 
     LIMIT $batchSize",
    [$threshold]
);

if (empty($quoteIds)) {
    echo "No stale quotes found.\n";
    exit;
}

$connection->beginTransaction();
try {
    // Delete child tables first
    foreach (['quote_item_option', 'quote_address_item', 'quote_shipping_rate', 'quote_payment'] as $table) {
        $connection->delete($table, ['item_id IN (SELECT item_id FROM quote_item WHERE quote_id IN (?))' => $quoteIds]);
    }
    $connection->delete('quote_item', ['quote_id IN (?)' => $quoteIds]);
    $connection->delete('quote_address', ['quote_id IN (?)' => $quoteIds]);
    $connection->delete('quote', ['entity_id IN (?)' => $quoteIds]);
    $connection->commit();
    echo "Deleted " . count($quoteIds) . " stale quotes.\n";
} catch (\Exception $e) {
    $connection->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}
Enter fullscreen mode Exit fullscreen mode

Reduce Guest Quote Lifetime

Guest carts are the biggest source of quote bloat. Every visitor who adds a product to their cart and leaves creates a permanent quote record. You can reduce the lifetime of guest quotes specifically:

In etc/di.xml or via a custom module, override the quote lifetime for guest quotes:

<type name="Magento\Quote\Model\Quote\GarbageCollection">
    <arguments>
        <argument name="lifetime" xsi:type="number">7</argument>
    </arguments>
</type>
Enter fullscreen mode Exit fullscreen mode

This limits guest quotes to 7 days instead of the default 30. For B2B stores with logged-in customers only, you can set this even lower.

Step 5: Monitor Quote Table Health

Set up monitoring so you catch bloat before it becomes a problem:

-- Create a monitoring query for your dashboard
SELECT 
  'quote_health' AS metric,
  COUNT(*) AS total_quotes,
  SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_quotes,
  SUM(CASE WHEN is_active = 0 AND updated_at > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS recent_abandoned,
  SUM(CASE WHEN is_active = 0 AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS stale_abandoned
FROM quote;
Enter fullscreen mode Exit fullscreen mode

You can wrap this in a simple shell script that alerts when stale_abandoned exceeds a threshold:

#!/bin/bash
# check-quote-bloat.sh
THRESHOLD=50000

RESULT=$(mysql -u $DB_USER -p$DB_PASS $DB_NAME -N -e "
  SELECT COUNT(*) FROM quote 
  WHERE is_active = 0 
  AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
")

if [ "$RESULT" -gt "$THRESHOLD" ]; then
  echo "WARNING: $RESULT stale quotes (threshold: $THRESHOLD)"
  # Send alert via Slack, email, etc.
fi
Enter fullscreen mode Exit fullscreen mode

Real-World Case Study

A mid-size B2C fashion retailer came to us with checkout pages taking 6–8 seconds to load. The frontend was fully cached with Varnish, so catalog and product pages were fast. But checkout — being dynamic — was uncached and suffering.

Diagnosis:

  • quote table: 8.2M rows (7.9M inactive)
  • quote_item table: 22M rows
  • quote_item_option table: 45M rows
  • Total quote-related storage: 12GB

Actions taken:

  1. Archived all inactive quotes older than 60 days (7.1M quotes)
  2. Batched deletes over 4 hours during a maintenance window
  3. Ran OPTIMIZE TABLE on all quote tables (reclaimed 9GB)
  4. Added composite index on (is_active, updated_at)
  5. Set up weekly cleanup cron with 30-day threshold
  6. Reduced guest quote lifetime to 7 days

Results:

  • Checkout page load: 6.5s → 1.4s
  • Cart page load: 4.2s → 0.9s
  • Database size reduced by 9GB
  • No recurrence after 6 months (quote table stays under 200K rows)

Common Pitfalls

Don't Delete Quotes with Orders

Always check that a quote hasn't been converted to an order before deleting:

-- Safe delete check
SELECT COUNT(*) FROM quote q
LEFT JOIN sales_order so ON q.entity_id = so.quote_id
WHERE q.is_active = 0 
AND q.updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
AND so.entity_id IS NULL;
Enter fullscreen mode Exit fullscreen mode

Magento stores quote_id in sales_order, so this join is reliable.

Don't Forget the Search Temp Table

Magento creates temporary search tables during quote loading. If these are large, they can cause issues:

-- Check for orphaned search temp tables
SHOW TABLES LIKE 'search\_tmp\_%';
Enter fullscreen mode Exit fullscreen mode

Don't Run OPTIMIZE TABLE on a Primary During Peak Hours

OPTIMIZE TABLE on InnoDB creates a temporary copy of the table. On large tables, this can consume significant disk space and I/O. Run it during maintenance windows or on a replica first.

Conclusion

Quote table bloat is one of the most overlooked performance issues in Magento 2. It doesn't show up in frontend cache hit rates or Lighthouse scores — it shows up in checkout conversion rates, which is where your revenue lives.

The fix is straightforward: audit, archive, delete in batches, optimize, and automate. A weekly cleanup cron plus a reduced guest quote lifetime will keep your quote tables lean and your checkout fast.

TL;DR checklist:

  • ✅ Check quote and quote_item row counts
  • ✅ Archive inactive quotes older than 60–90 days
  • ✅ Delete in batches to avoid table locks
  • ✅ Run OPTIMIZE TABLE to reclaim space
  • ✅ Add composite indexes for common queries
  • ✅ Set up weekly cleanup cron
  • ✅ Reduce guest quote lifetime to 7 days
  • ✅ Monitor stale quote count with alerting

Your checkout page — and your conversion rate — will thank you.

Source: dev.to

arrow_back Back to Tutorials