Estou executando o mysql 5.6 e estou tentando otimizar uma tabela grande sem que o mysql a recrie. Como uma simulação, executei o mesmo comando (otimizar tabela) em uma tabela menor no mesmo banco de dados e o mysql insiste em recriar + analisar. Observe que estou usando o formato barracuda e arquivo por tabela. Não é possível otimizar sem recriar neste cenário?
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)
Você já está usando o formato innodb de arquivo por tabela , que é uma ótima maneira de evitar um arquivo ibdata enorme. Dessa forma, você já está seguindo uma prática recomendada que é útil em muitas situações.
Eu só tive que usar o comando OPTIMIZE no MySQL para recuperar espaço. Em última análise, esse espaço acaba voltando para a mesa porque provavelmente precisará desse espaço novamente de qualquer maneira.
Portanto, na minha opinião, a menos que você precise liberar espaço em disco, não precisa se preocupar com o comando OPTIMIZE para desempenho.
É sempre uma boa ideia seguir as práticas recomendadas, mas elas não devem ser aplicadas cegamente a todas as situações.
No caso de OPTIMIZE, sua utilidade em minha experiência é limitada a devolver espaço ao sistema operacional de tabelas que possuem grandes quantidades de espaço não utilizado alocado.
Não notei grandes diferenças nos tempos de execução da consulta antes ou depois de executar o OPTIMIZE. Talvez alguém no stackexchange tenha uma opinião diferente.