I dreamt that I wrote a MySQL query that messed up the text in my posts table so I checked that database backups were enabled – yes, nightly. Good.
I've recently cleared out a load of old cruft from the WordPress posts table (post revisions, attachments, and the like) and it was showing as 20.5 MB with a large overhead – time to optimise my tables. That shrunk it down to around 10 MB.
There would still, however, be a lot of information hanging around that I no longer need. So, checking the code for the Archive page, I was using only five columns:
- ID
- post_date
- post_title
- post_content
- post_type
Ever the impetuous fool, I dived in and removed all other columns. Refresh the Archive page, all working. Try On This Day, nothing. Refresh, nope. Once again, still nothing.
Checking the code I realised I was also using the post_name column to build redirect URLs and had just deleted all of that data. 😊
It's a good job I'd checked the backups.
Backup duly restored I went back through the process, keeping all the required columns, and everything works once again. The old posts table is down to 3.5 MB – not a bad return.
A similar process on the old WordPress comments table has reduced it to 1.9 MB and my total database size is down to 7.2 MB from around 27. Much better.
