MyBB Community Forums

Very nice explanation! Thanks for the information.

(2011-06-12, 01:13 AM)Mr. E Wrote: [ -> ]Very nice explanation! Thanks for the information.

Thanks and no problem.

(2011-06-11, 12:57 AM)Malcolm. Wrote: [ -> ]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.

All right, thanks!

Are you describing my forum? :p

No, I just said that so the next 500 admins with small forums to read this thread don't waste their time.

hi mysql in the round 3 error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqlcheck --databases [DATABASE] --optimize -h[HOST] -u[USER] -p[PASSWORD]' at line 1

(2011-06-18, 02:24 PM)hamed.gh Wrote: [ -> ]hi mysql in the round 3 error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqlcheck --databases [DATABASE] --optimize -h[HOST] -u[USER] -p[PASSWORD]' at line 1

It's not an SQL query. It's a bash command.

please example

Unless you have shell access, forget about it.

(2011-06-10, 11:35 PM)Malcolm. Wrote: [ -> ]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';

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.

Sorry for the noob question but may i ask if this will remove something from my forum like files? is this safe 100% to do this? and how often? thanks.

(2011-06-21, 01:37 PM)bestblackhatforum.com Wrote: [ -> ]Sorry for the noob question but may i ask if this will remove something from my forum like files? is this safe 100% to do this? and how often? thanks.

I have explained everything that this does. Read the thread again.