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.

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 ?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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:

share|improve this answer
    
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 Apr 12 '13 at 17:22
    
That would be faster, plus the added bonus of having the backup of the original MyISAM table. –  RolandoMySQLDBA Apr 12 '13 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 Apr 13 '13 at 17:27
    
While I have an answer, it would be better if you posted that as a separate question. –  RolandoMySQLDBA Apr 13 '13 at 18:42
    
dba.stackexchange.com/questions/39938/… thanx again –  max Apr 14 '13 at 11:31

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.