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

Question

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?

Solution 1

That's really an informational message.

Likely, you're doing OPTIMIZE on an InnoDB table (table using the InnoDB storage engine, rather than the MyISAM 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."

Solution 2

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                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

Solution 3

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>;

Solution 4

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.

View more solutions

Comments

[Vikrant More]

ok, could you please share the different way that you are doing.

[spencer7593]

I'm sure this is in the MySQL Reference Manual somewhere; this is expected behavior, and nothing to be concerned about. (Except that the table will be "locked" and be unavailable while the process runs to completion, which can take a while for a HUGH JASS table.) Reference: https://dev.mysql.com/doc/refman/5.5/en/optimize-table.html See the "InnoDB details" section.

[Philip Olson]

You can also use MySQL Workbench to perform the table optimization. See the Schema and Table Inspector documentation for additional information. Notice the "Optimize Table" option.

[jerclarke]

My question is why some InnoDB tables get this message, and others don't when I run it on all tables. Does it mean the ones without the message weren't fragmented?

[tachomi]

Excellent explanation for people that is initiating in db world. Thank you very much

[spencer7593]

@jeremyclark: In my experience, when I run OPTIMIZE TABLE on an InnoDB table (a valid tablename), the statement returns a resultset (table_name, op, msg_type, msg_text) with two rows. One row msg_type='note' with the message OP asked about, the other row msg_type='status' and msg_text='OK'. On large tables, the statement execution can take a long time. (Referring to versions before 5.7, before the introduction of the ALGORITHM=INPLACE behavior.) (Maybe the client you are using is "timing out" before the execution is completed? So you aren't seeing resultset returned? Just guessing.)

[Danny Staple]

Beware - do not use this if you are low on disk space as it is likely to cause your server to run out trying to recreate the very large table.

[Frits]

I would suggest putting together example code to go along with your answer. Have a look at how do I write a good answer

[Joel Murphy]

Does this method keep indexes and triggers?

[tquang]

Yes, of course. All of them! You can try it then use phpMyAdmin for checking again

[iwind]

Not really, MySQL will give the fail reason: Temporary file write failure, Operation Failed.

[DanB]

This doesn't recreate Foreign Key.

[Visal Varghese]

Woow, nice anwer

[Justin Levene]

Foreign keys are an issue with this solution