Switching from MyISAM to InnoDB

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Switching from MyISAM to InnoDB

Post by alex.barylski »

I have 20 or so tables used by a maintenance management system. Aerospace has so many caveats and exceptions to the rule of thumb, most of these tables are to accomodate these weird exceptions. Lots of many to many relationships.

It's very easy to lose yourself in the existing table schema, without first mapping out the relationships between various tables.

SqlYog has a schema diagram tool which will build a visual reference for you, BUT, it seems to assume InnoDB is used.

So I am starting to think:

1. Transactional support would be a bonus and make twice daily backups less critical
2. Foriegn key associations will let diagramming tools to build the map for me saving me tonnes of time (not to mention new developers)

What I would like to know, is whether forign key constrainst in InnoDB will have cascading deletes? When I remove a master record will all of it's associated tables/records be deleted as well? Will switching to InnoDB cause any issues, other than slightly less performance.

Cheers,
Alex
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Switching from MyISAM to InnoDB

Post by AbraCadaver »

You can set the foreign key constraint to RESTRICT, SET NULL or CASCADE for updates and deletes.

By the way, have you tried MySQL workbench? You can import your existing DB and it generates a schema. Might be worth a look.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Switching from MyISAM to InnoDB

Post by alex.barylski »

Lots of MySQL tools will generate schema diagrams, although I am assuming even MySQL monitor requires you to have used InnoDB tables and not MyISAM???

Cheers,
Alex
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Switching from MyISAM to InnoDB

Post by AbraCadaver »

PCSpectra wrote:Lots of MySQL tools will generate schema diagrams, although I am assuming even MySQL monitor requires you to have used InnoDB tables and not MyISAM???

Cheers,
Alex
MySQL Workbench will import myisam, obviously without the showing relations. But you can then use it to convert the tables and add your foreign keys graphically. Then you'll have your new diagram, with relations and a new database with innodb and foreign keys.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply