Database maintenance for stores with years of order history

Start with a backup you have restored

Everything below deletes rows or rebuilds tables. Take a dump first — and then prove it works, because an untested backup is a guess with a filename.

mysqldump --single-transaction --quick --routines --triggers \
  -u ocuser -p opencart | gzip > opencart-$(date +%F).sql.gz

# prove it: restore into a scratch database and compare
zcat opencart-$(date +%F).sql.gz | mysql -u ocuser -p opencart_restore_test

--single-transaction gives you a consistent snapshot of InnoDB tables without locking the store, so you can run it while customers are shopping. If the dump warns about non-transactional tables, that is a finding in itself — see the storage engine section below. And keep the file somewhere other than the server it came from; when a disk fails, it takes the store and the backup sitting next to it.

Backup flag that matters

--single-transaction

Safe delete batch

5,000 rows

Slow query threshold

long_query_time = 1

Find what is actually big

Do not start from a checklist of table names, including this one. Every store bloats differently, depending on which reports were left on and which extensions log to their own tables. One query tells you where to spend the afternoon:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024) AS mb,
       table_rows,
       engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;

table_rows is an estimate for InnoDB, which is fine here — you are looking for the tables an order of magnitude larger than the rest. In a store that has been running for years, the top of that list is almost never products or orders.

The tables that grow while nobody looks

OpenCart writes several logs that no scheduled job ever cleans. They are harmless individually and enormous after five years of bots and traffic:

• oc_customer_online — one row per visitor, written on page views when the “Customers Online” report is enabled in Settings → Option.
• oc_customer_activity — the customer activity log, controlled by the setting next to it.
• oc_customer_search — every search term typed into the store, feeding the search report.
• oc_session — session rows when the session engine is db; expired rows are not removed for you.
• oc_cart — abandoned carts, including guest carts from years ago.
• oc_api_session — API sessions created by admin order editing and integrations.

If you do not read the customers-online or activity reports, turn them off in the admin first. Cleaning a table that is still filling is a chore you will repeat every month. Then delete in batches rather than in one enormous statement, which would hold locks and bloat the redo log:

-- always look before you delete
SELECT COUNT(*) FROM oc_customer_online WHERE date_added < NOW() - INTERVAL 7 DAY;

-- then repeat until zero rows are affected
DELETE FROM oc_customer_online WHERE date_added < NOW() - INTERVAL 7 DAY LIMIT 5000;
DELETE FROM oc_session        WHERE expire < NOW() LIMIT 5000;
DELETE FROM oc_cart           WHERE customer_id = 0
                                AND date_added < NOW() - INTERVAL 60 DAY LIMIT 5000;

Once the first pass is done, put the same statements behind a nightly cron job with a smaller window. Cleanup done by hand is cleanup that gets skipped during the first busy week, and then you are back here in a year.

Order history: what not to delete

Order tables are the ones people most want to prune and should touch last. oc_order, oc_order_product, oc_order_option, oc_order_total and oc_order_history are your accounting record, and in most jurisdictions you are required to keep them for years.

There is one exception worth understanding. Rows with order_status_id = 0 are checkouts that reached the confirm step and never completed — abandoned payments. On a busy store they can outnumber real orders several times over. They are still not free to delete: a late callback from a payment provider can complete one hours after the fact, and support may need the record to explain a card charge. We only remove them past a generous window, in batches, after checking the provider has nothing pending.

If the order tables genuinely are the problem, the answer is archiving rather than deleting — move rows older than the retention period into an archive database that the storefront never queries.

The indexes OpenCart does not ship

This is where “large” turns into “slow”. OpenCart's default schema is sized for a few thousand products; past that, the category and search queries start scanning. Look at the plan before you add anything:

EXPLAIN SELECT p.product_id
FROM oc_product p
JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
JOIN oc_product_description pd ON pd.product_id = p.product_id
WHERE p2c.category_id = 20 AND pd.language_id = 1 AND p.status = 1
ORDER BY pd.name
LIMIT 20;

SHOW INDEX FROM oc_product_to_category;

A type of ALL on a large table, or “Using filesort” on the name sort, is the signal. Three indexes cover most of it — the first two for the storefront, the third for an admin order list that has become unusable:

ALTER TABLE oc_product_to_category ADD INDEX idx_cat_prod (category_id, product_id);
ALTER TABLE oc_product_description ADD INDEX idx_lang_name (language_id, name(64));
ALTER TABLE oc_order               ADD INDEX idx_status_added (order_status_id, date_added);

The name(64) part is a prefix index: it indexes the first 64 characters instead of the whole column, which keeps the index small enough to stay useful while still covering the sort. Add them one at a time and re-run EXPLAIN after each. Indexes are not free: every one of them is written on every insert and update, so a table with twelve indexes is slow in a different way. And check the SEO URL table while you are there — it is read on every single request, so an unindexed keyword lookup taxes the whole site.

OPTIMIZE, ANALYZE and the storage engine

Deleting millions of rows does not shrink the file on disk; InnoDB keeps the freed space for reuse. If you need it back, OPTIMIZE TABLE rebuilds the table — and it tells you so, because InnoDB does not implement optimize directly and falls back to a recreate plus analyze. It is a rebuild, so it belongs in a maintenance window, not in a cron job.

ANALYZE TABLE oc_product, oc_product_description, oc_order;   -- cheap, refreshes statistics
OPTIMIZE TABLE oc_customer_online;                            -- rebuild, reclaims space

-- tables that are not InnoDB, usually left over from OpenCart 1.5 / 2.x
SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

ANALYZE is the one to run routinely. After a large delete the optimiser's statistics describe a table that no longer exists, and it starts choosing bad plans on queries that were fine last week. Any MyISAM tables the last query finds should be converted: they ignore the InnoDB buffer pool and take a full table lock on every write.

Then let the database tell you

Once the obvious work is done, stop guessing. Turn on the slow query log with long_query_time = 1 and leave it running for a normal week, including a campaign day if you have one.

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- or, with the sys schema on MySQL 5.7+
SELECT db, exec_count, avg_latency, query
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

Order by total time, not by the single slowest query. A query taking 80 ms that runs on every page view costs a store far more than a two-second report somebody opens on Mondays — and it is usually an extension, not OpenCart core.

A routine worth automating

1. 1Nightly: prune the log tables in batches, and verify the backup ran and is not zero bytes.
2. 2Weekly: ANALYZE the large tables; skim the slow log for anything new.
3. 3Monthly: re-run the table size query and compare it with last month.
4. 4Quarterly: restore a backup into a scratch database and actually open the store against it.
5. 5After every extension install: check the size query again — new tables appear quietly.

None of this is difficult. It is just work that has no owner in most stores, which is exactly why the database is three times the size it needs to be by year four.

When to write to us

If you would rather not run DELETE statements against a live store, we do this as a bounded job: measure, propose the cleanup, run it on a restored copy first, then apply it with a rollback plan. Billed hourly at the $10 + VAT launch rate, first response within two hours, Monday to Saturday 09:00–22:00 (GMT+3).