Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I actually have a few issues.

First, I found this information about optimizing: MySQL OPTIMIZE all tables?

The problem is, I don't seem to have a mysqlcheck program anywhere. I'm using MySQL Workbench. It's possible I have used other MySQL versions in the past. When I look for mysql I find in the Program Files folder there is a MySQL folder with a MySQL Server 5.5 folder. This one has some .ini files and the bin, dat, etc directories, but no executables. Then in Program Files (x86) there is another MySQL folder. In that there are several folders including: MySQL Documentation 5.5.30, MySQL Installer, MySQL Notifier, MySQL Workbench CE 5.2.47, and Samples and Examples 5.5.30. The only one that has mysql.exe is MySQL Workbench CE 5.2.47. But it does not have mysqlcheck.exe.

I poked around in Workbench and could not find any maintenance or optimization functions.

My database is 17 GB and I removed some large text fields from large tables, and I'm hoping to recover space because my OS is yelling at me to make more space. Does Workbench have the capability to optimize, and if so how do I do it? And if not, what can I install to be able to do it?

share|improve this question

migrated from stackoverflow.com Jan 22 at 4:56

This question came from our site for professional and enthusiast programmers.

    
Current version of MySQL Workbench is 6.0.8. There is also a set of utilities that are available as a separate download. AFAIK there's nothing that will optimise automatically - you need to do that bit yourself. –  Mike W Jan 22 at 3:28
    
What do you mean do it myself? I removed all the extra data, I just don't know how to shrink the file. –  Michael T Jan 22 at 3:31
    
There's no magic 'optimise' button. If you want to run a command you need to tell WorkBench what you want to do. –  Mike W Jan 22 at 3:37
    
In any case, both this and your other related question are really better asked on Database Administrators –  Mike W Jan 22 at 3:40
    
I guess I'm asking how to word such a command to make workbench do it. Would it be best to export all the data into text files, then run those commands to completely rebuild the database? Or is there a simpler method? –  Michael T Jan 22 at 3:41

2 Answers 2

The maintenance feature is a bit hidden in MySQL Workbench. You can reach it opening your connection as usual and right clicking on your schema to open the schema inspector:

enter image description here

You will get a new tab in the editor area with many pages for inspection/maintenance. Click on the Maintenance button at the bottom to open this page:

enter image description here

From there it should be clear what to do.

share|improve this answer
    
That option doesn't seem to exist in the version of Workbench I'm using (5.2.47 CE). I think I will need to upgrade it first. But question before I do - will this actually make the idbdata1 file smaller? In another post somebody said it will not. –  Michael T Jan 23 at 2:34
1  
Upgrading to a 6.x version is a good idea. The size of the ibdata file depends on the server not MySQL Workbench, so no, upgrading MySQL Workbench won't affect any server data in any way. –  Mike Lischke Jan 23 at 7:50

Run optimize table against your tables.This command defragments a table after you have deleted a lot of rows from it. http://dev.mysql.com/doc/refman/5.0/en/optimization.html

You can run this command even using MySQL workbench.

You can find out useful commands related to Table diagnostic from here: http://dev.mysql.com/doc/refman/5.0/en/table-maintenance-sql.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.