1

I'm running mysql 5.6 and I am trying to optimize a large table without mysql recreating it. As a dry run I ran the same command (optimize table) on a smaller table in the same DB and mysql insists on a recreate+analyze instead. Note that I'm using barracuda format and file-per-table. Is it not possible to optimize without recreate in this scenario?

mysql> optimize table Users;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| rt4.Users | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| rt4.Users | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.43 sec)

mysql> desc Users;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name                  | varchar(200) | NO   | UNI | NULL    |                |
| Password              | varchar(256) | YES  |     | NULL    |                |
| AuthToken             | varchar(16)  | YES  |     | NULL    |                |
| Comments              | text         | YES  |     | NULL    |                |
| Signature             | text         | YES  |     | NULL    |                |
| EmailAddress          | varchar(120) | YES  | MUL | NULL    |                |
| FreeformContactInfo   | text         | YES  |     | NULL    |                |
| Organization          | varchar(200) | YES  |     | NULL    |                |
| RealName              | varchar(120) | YES  |     | NULL    |                |
| NickName              | varchar(16)  | YES  |     | NULL    |                |
| Lang                  | varchar(16)  | YES  |     | NULL    |                |
| EmailEncoding         | varchar(16)  | YES  |     | NULL    |                |
| WebEncoding           | varchar(16)  | YES  |     | NULL    |                |
| ExternalContactInfoId | varchar(100) | YES  |     | NULL    |                |
| ContactInfoSystem     | varchar(30)  | YES  |     | NULL    |                |
| ExternalAuthId        | varchar(100) | YES  |     | NULL    |                |
| AuthSystem            | varchar(30)  | YES  |     | NULL    |                |
| Gecos                 | varchar(16)  | YES  |     | NULL    |                |
| HomePhone             | varchar(30)  | YES  |     | NULL    |                |
| WorkPhone             | varchar(30)  | YES  |     | NULL    |                |
| MobilePhone           | varchar(30)  | YES  |     | NULL    |                |
| PagerPhone            | varchar(30)  | YES  |     | NULL    |                |
| Address1              | varchar(200) | YES  |     | NULL    |                |
| Address2              | varchar(200) | YES  |     | NULL    |                |
| City                  | varchar(100) | YES  |     | NULL    |                |
| State                 | varchar(100) | YES  |     | NULL    |                |
| Zip                   | varchar(16)  | YES  |     | NULL    |                |
| Country               | varchar(50)  | YES  |     | NULL    |                |
| Timezone              | varchar(50)  | YES  |     | NULL    |                |
| PGPKey                | text         | YES  |     | NULL    |                |
| Creator               | int(11)      | NO   |     | 0       |                |
| Created               | datetime     | YES  |     | NULL    |                |
| LastUpdatedBy         | int(11)      | NO   |     | 0       |                |
| LastUpdated           | datetime     | YES  |     | NULL    |                |
| SMIMECertificate      | text         | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
36 rows in set (0.00 sec)

mysql> select @@VERSION;
+------------+
| @@VERSION  |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.00 sec)
  • What are you trying to accomplish exactly? The OPTIMIZE command in MySQL terms just means reclaiming space and reorganizing physical storage of data. So are you trying to reclaim space or make your queries run faster? – Craig Efrein Jul 4 '14 at 9:50
  • Why passwod has 256 chars? Do you store password in plain text? – Mateusz Nowak Jul 4 '14 at 21:29
  • @CraigEfrein I'm trying to perform best practices DB maintenance. – Ram Jul 5 '14 at 17:09
  • @Helid: 1) Not in plain text. 2) Not my software Request Tracker 4.2 [a massively popular service ticket system]. – Ram Jul 5 '14 at 17:10
1

You are already using the file-per-table innodb format which is a great way to avoid a huge ibdata file. In this way, you are already following a best practice that is useful in many situations.

I've only had to use the OPTIMIZE command in MySQL to reclaim space. Ultimately, that space ends up going right back to the table because it will probably need that space again anyway.

So in my opinion, unless you need to free up disk space, you don't have to worry about the OPTIMIZE command for performance.

Its always a good idea to follow best practices, but they shouldn't just be applied blindly to every situation.

In the case of OPTIMIZE, its usefulness in my experience is limited to giving space back to the OS from tables that have large amounts of allocated unused space.

I have not noticed any huge differences in query execution times before or after running OPTIMIZE. Maybe someone else on stackexchange will have a different opinion.

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.