Reducing a database without losing records.
WHY
A shop with years of orders, or a site with years of logs, accumulates data that is rarely read and slows everything.
THE OPTIONS
Export and remove, keeping the export as a record Move to an archive table, which keeps it accessible and out of the main query path Delete, where the data has no ongoing value
WHAT CAN USUALLY GO
Old log entries Expired sessions Old statistics, where you have summaries Spam Very old revisions
WHAT CANNOT
Orders and financial records, which frequently have retention obligations Customer records still in use
TAKE ADVICE
Retention requirements vary. Speak to an accountant about what your business must keep.
THE PROCEDURE
- Back up.
- Export what you are removing, and store it off the server.
- Delete in batches rather than one enormous statement, which locks the table.
- Optimise the table afterwards to reclaim space.
BATCHING
DELETE with a LIMIT, run repeatedly, is gentler on a busy site than removing a million rows at once.
