Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you have all InnoDB tables, all MyISAM tables, or a mixture of both ? –  RolandoMySQLDBA Apr 24 '13 at 11:22
    
A mixture. Does that matter? –  Mathias Lykkegaard Lorenzen Apr 24 '13 at 11:23
add comment

1 Answer

up vote 2 down vote accepted

OPTIMIZE TABLE basically does three(3) things

  1. Shrinks the data pages
  2. Shrinks index pages
  3. 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 know 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* disabled, 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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.