What a difference a simple cleanup and optimization makes! If you are using PhpMyAdmin, follow these simple steps (after safely backing up your data).
Click on the database you are working with on the left hand side frame (not the table, the database name)
On the right hand column your tables should be listed. Scroll down till you see the “Check all” link.
Click on that link, make sure all database tables are checked and then from the dropdown next to it, carefully select “Optimize table”.
This should perform an analysis, repair and an optimization of your database. Most problems will be solved with this. In rare cases you might have to rebuild indices, but thats a huge topic, too dangerous to perform without looking at individual cases and thus I will not preach it here. (My database corruption was one of those rare cases) Please backup your data before any database manipulation and I will not be resposible for any mistakes or loss of data.
PS: Another fairly certain method of fixing your database/tables with PhpMyAdmin is to export your data, delete your existing tables and then re-import. PhpMyAdmin performs certain optimization tasks on the data that are very beneficial.
So, any advice on how to go about upgrading a MySQL 3.23.47 server to a newer (4.x) version? I’ve been wanting to to that for a long time, but the last time I looked into it, I seem to recall that it looked complicated. And I’m lazy.
Mark, how often do you optimize your database? Also, what tool are you using to get the above graph?
I have way too many and I am in the databases enough that I really cannot say with confidence. The reason for this fix was a full text index created for a plugin that I had mentioned previously (don’t want to bring it up again, not really consequential). However, knowing mysql, every couple of years is a good time table for the casual blogger. Databases such as yours will need much more frequent maintenance.
This graph is created by a tool called rrdtool that I custom scripted to record and graph various system parameters of my server. What you see there is a weekly graph of processor usage calculated from uptime results.
Dougal, im still in the 3 point releases and have not upgraded. 🙂
rrdtool, eh? Plan on releasing it? 😉
Thank You so much for this – it works like a dream.
thanks – this worked great!
very useful info, i am tryng to optimize my database right now =)
Thanks! I cut 2+ MB off of my database using your instructions!