Database Optimization - What it is and why to do it
#1
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.

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. Smile

No longer involved in the MyBB project.
Reply
#2
Nice explanation. Smile
--Pyridine
Reply
#3
Optimizing from the ACP gives the same result?
Reply
#4
Exactly the same result as doing it from phpMyAdmin, but more convenient for the administrator.
No longer involved in the MyBB project.
Reply
#5
(2011-06-11, 12:44 AM)Malcolm. Wrote: Exactly the same result as doing it from phpMyAdmin, but more convenient for the administrator.

Ok, thanks for the tut Smile I do it every now and then from ACP Big Grin
Reply
#6
I recommend doing it at least once a month, but obviously if your forum only gets 3 posts a month, there's not much point doing it. Toungue
No longer involved in the MyBB project.
Reply
#7
Nice tip!
Reply
#8
Thanks faviouz. Smile
No longer involved in the MyBB project.
Reply
#9
Can this be done automatically by schedule?
Reply
#10
You could set up a cron job to do that.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)