I use InnoDB storage engine in my projects because of its support for transactions and referral integrity rules. However, MySQL still creates new tables as MyISAM by default. It was so annoying for me to always define storage engine when creating new tables and double check that I didn’t forget it until I found how I can set InnoDB as default.

The solution is very easy:

  1. Open your MySQL configuration file: my.cnf or my.ini depending on your operation system.
  2. Locate [mysqld] section which usually goes at the top of file.
  3. Add this setting to the section:

    default-storage-engine = InnoDB

  4. Restart MySQL server.
  5. Enjoy your new new tables created as InnoDB w/o any additional worry.

The setting is applied to CREATE TABLE queries as well as tables created with phpMyAdmin, navicat and possibly any other MySQL client.

If you can’t change MySQL configuration file you still can change default storage engine used during a session by setting storage_engine system variable:

SET storage_engine=InnoDB;

More Info

http://www.innodb.com/

http://www.innodb.com/wp/products/innodb/

http://en.wikipedia.org/wiki/InnoDB

http://dev.mysql.com/doc/refman/5.5/en/innodb.html

http://dev.mysql.com/doc/refman/5.5/en/table-locking.html

http://tag1consulting.com/InnoDB_Performance_Tuning

http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/


Advertisements