Storage engine of MySql: MyISAM and InnoDB

Any DBMS software has at least Query parser, Front-end client, and storage engine. Storage engine is the core part of the DBMS system where your tables resides.

With MySQL, we can change the underlying storage engine -
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

"MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default."

MyISAM is high-speed and manages non-transactional tables.
But, If you want Transactional support and Primary/Foreign key support you need to use InnoDB.

Also look out Falcon storage engine with Mysql 6.0 which is both high-speed and provides transactional support and other functionality - http://dev.mysql.com/doc/refman/6.0/en/se-falcon.html

InnoDB table example: (ENGINE=InnoDB at the end of create statement)

CREATE TABLE `databasename`.tablename'
(
id int PRIMARY KEY NOT NULL,
name varchar(40),
) ENGINE=InnoDB;

Before running the above script, make sure INNODB support is enabled for your MySQL server
Go to Installation Dir\bin\my.ini
Comment out -- #skip-innodb
Also make "default-storage-engine=INNODB" in the same file so you do not have to type ENGINE=InnoDB in each CREATE statements.