150

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?

bandrade
  • 143
  • 1
  • 6
Vikrant More
  • 5,182
  • 23
  • 58
  • 90

5 Answers5

277

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • 20
    ok, could you please share the different way that you are doing. – Vikrant More Jun 04 '15 at 05:56
  • 1
    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](https://dev.mysql.com/doc/refman/5.5/en/optimize-table.html) See the "InnoDB details" section. – spencer7593 Jun 04 '15 at 05:57
  • You can also use MySQL Workbench to perform the table optimization. See the [Schema and Table Inspector documentation](https://dev.mysql.com/doc/workbench/en/wb-develop-object-management-inspector.html#wb-develop-object-management-schema-inspector) for additional information. Notice the "Optimize Table" option. – Philip Olson Jun 04 '15 at 21:54
  • 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? – jerclarke Jun 01 '16 at 17:08
  • 3
    Excellent explanation for people that is initiating in db world. Thank you very much – tachomi Sep 13 '16 at 22:07
  • @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.) – spencer7593 Sep 13 '16 at 22:45
  • 7
    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 Sep 26 '16 at 11:22
  • 1
    Woow, nice anwer – Visal Varghese Aug 26 '20 at 06:18
29

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                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
sdesvergez
  • 815
  • 8
  • 3
  • 2
    Not really, MySQL will give the fail reason: Temporary file write failure, Operation Failed. – iwind May 08 '19 at 03:19
  • Read the accepted answer, you need a lot disk space when you run `OPTIMIZE TABLE` on a very large InnoDB table as a temporary table (where you get that error message from!) is being created to copy all data over there, drop the old one and rename the temporary back to the original one. Then it runs `ANALYZE TABLE` on it. – Roland Jan 28 '23 at 10:30
17

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>;
tquang
  • 583
  • 4
  • 12
1

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.

aequalsb
  • 3,765
  • 1
  • 20
  • 21
Pedro Pinheiro
  • 11
  • 1
0

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,

ASHOK MANGHAT
  • 35
  • 2
  • 4
    I would suggest putting together example code to go along with your answer. Have a look at [how do I write a good answer](http://stackoverflow.com/help/how-to-answer) – Frits Sep 30 '16 at 13:51