Database File Size

I can’t honestly remember how many posts I had before the upgrade, otherwise that’d be a good way to figure it out. I think I emptied the akismet spam, too — but I doubt it would make that huge of a difference.

Could always look at the backup of your database you made before you did the upgrade…

I’m a db dunce. How do I look at the file?

Use any file editor (assuming you have a .sql type export from something like phpMyAdmin)

I did export the file as sql and have opened it in a text editor. Considering it’s a lot of data, what do I look for?

Look for the last post you made…
Look for wp_posts table and get an idea of how many posts you had–does it ‘check’ with what your blog now shows?

Would this be it?

VALUES(3025, 1, ‘2008-06-23 09:12:12’, ‘2008-06-23 14:12:12’, ‘

Does that mean I have 3025 posts total combining published and draft?

Actually, I was just recommending looking to see if can find familiar text for the posts in the database backup and see if there is a related post on your current blog.

The ‘3025’ is likely the ID field…

Well, I have 2,693 published entries and 23 drafts, so it doesn’t quite add up to 3000. But I confirmed the first and last posts are in the file — what could account for the discrepancy? Thanks for being responsive, Michael.

Could be the database was optimized, could be you deleting a bunch of spam.

Can it really optimize by more than twice the size? It went from 50 to 17megs. I did have a db upgrade during the upgrade process.

Guess you could always restore you backup to ANOTHER database and compare each table’s record counts between the two databases.

Might also ask your host if anything happened on their end.

Well, I have 2,693 published entries and 23 drafts, so it doesn’t quite add up to 3000. But I confirmed the first and last posts are in the file — what could account for the discrepancy?

File attachments (like picture uploads). Those take up a Post as well. So do Pages. And starting with version 2.6, so will Post Revisions.

Can it really optimize by more than twice the size?

Yes. Quite easily, in point of fact. Databases are different than normal file systems, the same rules don’t apply. And when you do an upgrade, with something that alters the table, then the database will be optimized in that process. This is normal, pay it no mind.

Thanks for the peace of mind and explaining the discrepancy in the numbers, Otto.