Ошибка table does not support optimize doing recreate analyze instead

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.»

EDIT

This needs to reviewed for accuracy. Observed behavior from MySQL 5.7 and also on MariaDB 10.3
Point to emphasize, InnoDB and MyISAM are two different storage engines. They operate differently. Is why the messags we see is different.

We are issuing the DDL statements to MySQL database server. The MySQL server receives the statement, does the usual syntax check parsing, tokens, … and resolves references to tables, columns with lookups in the dictionary.

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.
—Danny Staple

Yes, beware diskspace limitation. And how long operation will hold EXCLUSIVE (blocking) lock on the table. If we understand what operations the optimizer is coming up with; typically for large volume of data, I opt for partitioning, which allows operating on standalone tables that can be swapped into (REPLACE) an existing partition.

ASPECT #1

The message you saw did not say

«Table does not support optimize, do recreate + analyze instead
OK«

but the message said

«Table does not support optimize, doing recreate + analyze instead
OK«

This means the InnoDB Storage Engine already executed

ALTER TABLE GEO ENGINE='InnoDB';
ANALYZE TABLE GEO;

There was no need to do it again.

ASPECT #2

There is a reason why fragmentation still exists

If the primary key of the table is an integer with auto_increment, all the numbers generated for the primary key are in ascending order.

When the primary key was being loaded into the BTREE index, fragmentation was made. Why?

You may find this surprising, but I wrote an answer to a post about 3 years ago entitled How badly does innodb fragment in the face of somewhat out-of-order insertions?

In that post I explained how AVL binary trees will cause height rotation about 45% of of the time. For a BTREE, this would be represented as BTREE page splits. What does means for sorted data ?

• When loading data in ascending order, tree pages that split to the left would always be half full
• When loading data in descending order, tree pages that split to the right would always be half full

Those half full pages is what is manifesting the fragmentation.

This is further illustrated in another post I wrote last year (Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?) where I mentioned this same issue for MyISAM.

CONCLUSION

The only way to really reduce fragmentation would be to drop all indexes and recreate the indexes. If the tables are gigantic, this is not worth your time.

Problem Description:

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.»

EDIT

This needs to reviewed for accuracy. Observed behavior from MySQL 5.7 and also on MariaDB 10.3
Point to emphasize, InnoDB and MyISAM are two different storage engines. They operate differently. Is why the messags we see is different.

We are issuing the DDL statements to MySQL database server. The MySQL server receives the statement, does the usual syntax check parsing, tokens, … and resolves references to tables, columns with lookups in the dictionary.

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.
–Danny Staple

Yes, beware diskspace limitation. And how long operation will hold EXCLUSIVE (blocking) lock on the table. If we understand what operations the optimizer is coming up with; typically for large volume of data, I opt for partitioning, which allows operating on standalone tables that can be swapped into (REPLACE) an existing partition.

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

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,

Solution – 4

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 – 5

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.

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

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;