Mysql – extra steps after changing storage engine and adding index

innodbmyisamMySQLoptimization

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases.

I have a php/MySQL site uses the MyISAM storage engine. It doesn't have any indexes on the columns.

I want to change the engine to innoDB and add indexes to the columns.

Some tables have a couple hundred thousand rows so it's not a very small database.

My question is mostly about the data that already is in the database. Do I need to do anything after these changes to make the already stored data aware of that ? or make them compatible with these changes ?

Best Answer

You came to right place. Let's look at an example.

Suppose you have the following MyISAM table:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    firstname varchar(20),
    lastname varchar(20),
    straddr varchar(40),
    city varchar(40),
    state char(2),
    primary key (id)
) ENGINE=MyISAM;

You said you want to do two things:

  1. Change the table to InnoDB
  2. Add an index

For the above table, here is how to convert the table to InnoDB and add an index on names

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ALTER TABLE mydb.mytable ADD INDEX name (lastname,firstname);

Here is a way to make the table InnoDB, index it, and keep the original as MyISAM

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
ALTER TABLE mydb.mytablenew ENGINE=InnoDB;
ALTER TABLE mydb.mytablenew ADD INDEX name (lastname,firstname);
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytableold;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;

CAVEAT

This answer is really an oversimplification of the conversion. There are still other aspects of setting up InnoDB (Here is a Pictorial Representation of InnoDB's Infrastructure). Here are a few of my past posts on such configuration due diligence:

Related Question