4

I actually have a few issues.

First, I found this information about optimizing: https://stackoverflow.com/questions/5474662/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?

  • 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 '14 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 '14 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 '14 at 3:37
  • In any case, both this and your other related question are really better asked on Database Administrators – Mike W Jan 22 '14 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 '14 at 3:41
5

The maintenance feature is a bit hidden in MySQL Workbench. You can reach it by 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.

  • 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 '14 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 '14 at 7:50
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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