Solved

Optimize MYSQL Database

Posted on 2013-06-19
4
782 Views
Last Modified: 2013-06-25
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!!
0
Comment
Question by:CompTech810
4 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39260613
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.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39260879
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.).
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
ID: 39273805
I also if there is any database in which removing white space would be called optimization, perhaps compression, as in MS Access. But even there it's not about the whitspace in text fields, it's about removing garbage, deleted rows, etc.

Optimization is about indexes, most of the time, not about the data itself.

Also you can only remove whitespace from variable length fields.

What performance advantage do you expect? Data transfer typically is compressed automatically at leas on the http request level, where one header defines accpeted compressions like gzip, deflate.

Bye, Olaf.
0
 
LVL 2

Author Comment

by:CompTech810
ID: 39274487
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question