2011-06-10, 11:35 PM
I don't think that a lot of people actually know what optimizing a database does, so I'm creating this little explanation thread to make it easy to understand.
All database information is stored in files. When a query to delete a post, thread, member, or any other piece of data is executed, it removes the data from the file.
When this happens, it leaves empty space in the file which takes up a few bytes. After time, this space adds up and can impact on mysql performance.
What optimizing the database does is it reclaims this space, and more often than not reduces your database size.
If you have phpMyAdmin installed on your server, then you can see how much empty space are in your tables by selecting your forum's database and looking at the Over Head column.
If you are like me, and prefer to use the mysql terminal, login, select your database and run the following query.
You should see a column named Data_free. This is the equivalent to Over Head in phpMyAdmin.
Any table that doesn't have this value as 0, has empty space and should be optimized.
There are different ways of doing this.
1 - From MyBB.
Admin CP > Tools & Maintenance > Optimize Database
2 - Via phpMyAdmin.
(I don't have phpMyAdmin installed, so this may not be 100% accurate)
phpMyAdmin > Forum Database > Select All Tables > Optimize Tables
3 - MySQL Terminal.
Run these queries.
To do this automatically without any hassle please visit faviouz's tutorial, How to automatically optimize all database tables via a task
That's basically the gist of it. If anyone would like to add any comments, or if you would like to add anything informational wise to the thread, please reply below.
All database information is stored in files. When a query to delete a post, thread, member, or any other piece of data is executed, it removes the data from the file.
When this happens, it leaves empty space in the file which takes up a few bytes. After time, this space adds up and can impact on mysql performance.
What optimizing the database does is it reclaims this space, and more often than not reduces your database size.
If you have phpMyAdmin installed on your server, then you can see how much empty space are in your tables by selecting your forum's database and looking at the Over Head column.
If you are like me, and prefer to use the mysql terminal, login, select your database and run the following query.
SHOW TABLE STATUS;
You should see a column named Data_free. This is the equivalent to Over Head in phpMyAdmin.
Any table that doesn't have this value as 0, has empty space and should be optimized.
There are different ways of doing this.
1 - From MyBB.
Admin CP > Tools & Maintenance > Optimize Database
2 - Via phpMyAdmin.
(I don't have phpMyAdmin installed, so this may not be 100% accurate)
phpMyAdmin > Forum Database > Select All Tables > Optimize Tables
3 - MySQL Terminal.
Run these queries.
mysqlcheck --databases [DATABASE] --optimize -h[HOST] -u[USER] -p[PASSWORD];mysql -u[USER] -h[HOST] -p[PASSWORD] [DATABASE] -e'flush tables';To do this automatically without any hassle please visit faviouz's tutorial, How to automatically optimize all database tables via a task
That's basically the gist of it. If anyone would like to add any comments, or if you would like to add anything informational wise to the thread, please reply below.
