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

mysql
Vikrant More picture Vikrant More · Jun 4, 2015 · Viewed 97.1k times · Source

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?

Answer

spencer7593 picture spencer7593 · Jun 4, 2015

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

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More