Why is NHibernate SchemaUpdate much slower in MySQL then in MS SQL Server

We use SchemaUpdate.Execute() to update the database schema structure on application upgrades in an NHibernate application which works in both the MySQL5Dialect and the MsSql2008Dialect. The database has about 220 tables. It takes only several seconds to execute against MsSql but may take 10 minutes or even more to execute against MySql. It doesn't seem to matter how many actual schema changes there are - it is the comparison that takes so long.

Has anyone else experienced slow performance on SchemaUpdate.Execute() for MySql? And is there anything I can do, either in the application, or the database, to optimize it?开发者_StackOverflow社区

Most likely this is occurring because you're not using innodb, which means ddl changes have to copy all the data and rebuild all the indexes.

Why is this faster on MsSql? Because it has proper ddl support.

Try changing your table store to innodb, I'm sure you'll be pleasantly surprised.