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 ?

1 Answer 1


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)

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;


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:

  • thank you , so what you say is i should make a new table with the same columns and innodb engine and add index to it and then transfer data from old table to new table ?
    – max
    Commented Apr 12, 2013 at 17:22
  • That would be faster, plus the added bonus of having the backup of the original MyISAM table. Commented Apr 12, 2013 at 17:23
  • thanx again , sorry if i'm insisting on this but i want to be sure . if i add an index to a column , it doesn't work/effect/index the data that already is in the table and i have to insert them again ?
    – max
    Commented Apr 13, 2013 at 17:27
  • While I have an answer, it would be better if you posted that as a separate question. Commented Apr 13, 2013 at 18:42
  • dba.stackexchange.com/questions/39938/… thanx again
    – max
    Commented Apr 14, 2013 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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