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