What does "Table does not support optimize, doing recreate + analyze instead" mean?

I am working on MySQL 5.5 and trying to do index rebuild using an OPTIMIZE TABLE query. I am getting the error below:

> Table does not support optimize, doing recreate + analyze instead

What does this mean? Is MySQL engine not allowing Index Rebuild? What is being done behind this message, at MySQL 5.5 Engine level?

That's really an informational message.

Likely, you're doing OPTIMIZE on an [InnoDB][1] table (table using the InnoDB storage engine, rather than the [MyISAM][2] storage engine).

InnoDB doesn't support the OPTIMIZE the way MyISAM does. It does something different. It creates an empty table, and copies all of the rows from the existing table into it, and essentially deletes the old table and renames the new table, and then runs an ANALYZE to gather statistics. That's the closest that InnoDB can get to doing an OPTIMIZE.

The message you are getting is basically MySQL server repeating what the InnoDB storage engine told MySQL server:

Table does not support optimize is the InnoDB storage engine saying...

"I (the InnoDB storage engine) don't do an OPTIMIZE operation like my friend (the MyISAM storage engine) does."

"doing recreate + analyze instead" is the InnoDB storage engine saying...

"I have decided to perform a different set of operations which will achieve an equivalent result."

[1]: http://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html "InnoDB" [2]: http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html "MyISAM"

OPTIMIZE TABLE works fine with InnoDB engine according to the official support article : http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

You'll notice that optimize InnoDB tables will rebuild table structure and update index statistics (something like ALTER TABLE).

Keep in mind that this message could be an informational mention only and the very important information is the status of your query : just OK !

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

Best option is create new table with same properties

CREATE TABLE <NEW.NAME.TABLE> LIKE <TABLE.CRASHED>;
INSERT INTO <NEW.NAME.TABLE> SELECT * FROM <TABLE.CRASHED>;

Rename NEW.NAME.TABLE and TABLE.CRASH

RENAME TABLE <TABLE.CRASHED> TO <TABLE.CRASHED.BACKUP>;
RENAME TABLE <NEW.NAME.TABLE> TO <TABLE.CRASHED>;

After work well, delete

DROP TABLE <TABLE.CRASHED.BACKUP>;

The better option is create a new table copy the rows to the destination table, drop the actual table and rename the newly created table . This method is good for small tables,

I know this is a very old topic/problem description but maybe a new approach can be performed.

I had the same issue with an InnoDB Engine table.

As "sdesvergez" said, the optmize works dispite the returned message saying otherwise. But we don't know what are the real consequences are in the background.

I am assuming your table is not too big (less than 1GB) like mine (200Mb).

I made a change in the table structure, instead of "pure" InnoDB I set the table with a single partition:

CREATE TABLE IF NOT EXISTS <<schema>>.<<table name>>(
<<your tabe definition>>
) PARTITION BY KEY(<<key from table, in my case I used "day">>)
PARTITIONS 1;

The table still works with the InnoDB engine, but it now has a deeper structure with the Partitions.

After you do so, you can can then rebuild the partition in order to optimize it.

The rebuild will lose the space allocated to the deleted records and also optimize the table. In my case this process took 10 seconds.

This way you don't get any strange messages in the status of the operation.

So far I have not had any data loss or any other problems using this method, but a very fast and organized table.