5

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?

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

2 Answers 2

Reset to default
6

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.

Improve this answer
3
  • 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
    Commented Jan 23, 2014 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
    Commented Jan 23, 2014 at 7:50
  • I had an issue using Table Maintenance Operations in MySQL Workbench 8.0. When attempting to Optimize InnoDB tables, I received the message "Table does not support optimize, doing recreate + analyze instead". Naturally, I assumed this meant I don't need to Analyze the tables because it says analyze was already done. But the db didn't recover any size despite deleting rows equivalent to hundreds of MegaBytes. After much trial and error, I eventually happened to click Analyze Table on an individual Table Inspector view as a last resort and bam! - the db size shrunk to reflect the changes
    – Karl Bishop
    Commented May 22 at 2:18
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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