Optimize MYSQL Database
Hello Experts,
Hoping someone with patience can walk me through optimizing a MYSQL database as I am new to MYSQL. I have never understood why developers don't clear white space in files as part of a general/common routine.
Anywho, the database that I'm trying to shrink/optimize white space is a Sonicwall Viewpoint MYSQL database = gmsdatabase.
I have the MySql workbench 5.2. I'm able to open the database but how do I optimize all tables in all databases? I have tried OPTIMIZE, MYSQLCHECK, it doesn't recognize the commands. I wish I could just right click on the database and select Optimize. You would think that something so common would be right there.... Uggghhhh
Thanks!!
Hoping someone with patience can walk me through optimizing a MYSQL database as I am new to MYSQL. I have never understood why developers don't clear white space in files as part of a general/common routine.
Anywho, the database that I'm trying to shrink/optimize white space is a Sonicwall Viewpoint MYSQL database = gmsdatabase.
I have the MySql workbench 5.2. I'm able to open the database but how do I optimize all tables in all databases? I have tried OPTIMIZE, MYSQLCHECK, it doesn't recognize the commands. I wish I could just right click on the database and select Optimize. You would think that something so common would be right there.... Uggghhhh
Thanks!!
Dave Baldwin
Here are the instructions http://dev.mysql.com/doc/refman/5.6/en/optimization.html but I'm not sure that they will do what you want. Standard database and table optomizations do not change the data.
Your definition of "optimize" seems to be different than mine. :)
Sounds like you got some junky data (remember: garbage in, garbage out).
You can do this on any columns of data that extra whitespace at the end that you don't need:
UPDATE table
SET bad_field = RTRIM(bad_field)
;
But if you have many fields and many tables this may be a pain in the butt (although I could easily whip up a dynamic query to *generate* the above UPDATE commands quite easily.).
Sounds like you got some junky data (remember: garbage in, garbage out).
You can do this on any columns of data that extra whitespace at the end that you don't need:
UPDATE table
SET bad_field = RTRIM(bad_field)
;
But if you have many fields and many tables this may be a pain in the butt (although I could easily whip up a dynamic query to *generate* the above UPDATE commands quite easily.).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
From different things I have read I was made to believe that there was some type of maintenance that should be done. I now believe that the size of the file is do to a change in data collected.
Thanks.
Thanks.