CODEWITHSUNDEEP
mysqldatabase
pghtech
pghtech

Reputation: 3702

Understanding the MySQL Engine vs Database Type?

So I am new to database MySql and Database configuration period. I originally deployed MySQl with the default Innodb engine and in turn created a database with single table through MySQL workbench.

However, I have decided to change to the MyISAM. If I changed the default engine to MyISAM, have I made the change to use MyISAM or do I have to recreate the database with the default engine setting changed?

Basically, I created a database with the default engine type of Innodb and want to run a MyISAM DB for my project.

Any help or clarification is greatly appreciated.

Upvotes: 1

Views: 1230

Answers (3)

Tom Neyland
Tom Neyland

Reputation: 6968

You need to ALTER the table that you created in order for it to be running on MyISAM.

ALTER TABLE MyTableName ENGINE = MYISAM;

Upvotes: 0

Alan Geleynse
Alan Geleynse

Reputation: 25139

The engine is per table, not per database.

Any tables that you created before changing the default storage engine will need to be converted over to the new type.

Only new tables will use the new default type unless you manually change the old ones.

You can change the storage engine using

ALTER TABLE TableName ENGINE=MyISAM

Upvotes: 1

Hammerite
Hammerite

Reputation: 22340

You can change the type of a table to MyISAM using

ALTER TABLE MyTable ENGINE=MYISAM

Note that this will give you an error message if you have defined any foreign keys on the table.

Also, if the table is large, it may take a while, because what MySQL does is create a new table of the chosen type, copy all the data from the old table into it, then delete the old table.

Edit: If you change the default engine, you haven't changed the engime for any tables already created. You have to explicitly change those tables.

Upvotes: 2

Related Questions