Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE? Will indexes and primary keys still remain the same? Are there areas of the database that will be slower after the optimization?

OPTIMIZE TABLE basically does three(3) things

• Shrinks the data pages

• Shrinks index pages

• Computes Fresh Index Statistics

Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable

USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp RENAME mytable;
DROP TABLE mytablezap;
ANALYZE TABLE mytable;

However, based on

• the table mydb.mytable

• the datadir being /var/lib/mysql

let's look at Storage Engine Specifics
MyISAM

The MyISAM table mydb.mytable is physically stored in three files

• /var/lib/mysql/mydb/mytable.frm (table structure)

• /var/lib/mysql/mydb/mytable.MYD (data)

• /var/lib/mysql/mydb/mytable.MYI (indexes)

The conceptual description of running OPTIMIZE TABLE would copy data pages and index pages into a new .MYD and .MYI. This will eliminate having fragmented pages in either file.
InnoDB

There are two viewpoints to take into consideration
Viewpoint #1 : innodb_file_per_table disabled

With innodb_file_per_table disabled, all data pages and index pages for every InnoDB table are stored inside the system tablespace (better known as the file ibdata1).

When you run OPTIMIZE TABLE on an InnoDB table that is stored in ibdata1, all the data and index pages are written contiguously so all the pages for the table are together. The bad news is that it makes ibdata1 grow rapidly.
Viewpoint #2 : innodb_file_per_table enabled

With innodb_file_per_table enabled, all data pages and index pages for every InnoDB table are stored outside ibdata1. Here is the physical storage of mydb.mytable:

• /var/lib/mysql/mydb/mytable.frm (table structure)

• /var/lib/mysql/mydb/mytable.ibd (data and indexes)

When you run OPTIMIZE TABLE on an InnoDB table that is stored outside of ibdata1 (system tablespace), this perform the conceptual steps that lead to shrinking the .ibd file.

I have written about this before

• Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (StackOverflow)

• Mar 25, 2012 : Why does InnoDB store all databases in one file?

• Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?

• Dec 21, 2012 : Is there a progress indicator for OPTIMIZE TABLE progress?

• Jan 07, 2013 : Database space doesn't match ibdata1 size

Now, for your original questions...

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

I just explained how OPTIMIZE TABLE works for both storage engines. This can be time-consuming depending on the size of the data and indexes. Outside of an Earthquake or Power Outage, running OPTIMIZE TABLE does not harm to the data and indexes get rebuilt.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE?

Ditto

Will indexes and primary keys still remain the same?

Yes

Are there areas of the database that will be slower after the optimization?

No way. Querying a table with no fragmentation can only be faster

StackExchange Database Administrators Q#40769, answer score: 21