1. Simplicity
MyISAM tables are simple. If you are new to MySQL or databases in general, I would recommend you start with MyISAM tables.
In the following example, we will create a new database and table from the mysql> command prompt. (You do have MySQL installed, right? If not, take a look at How to Install MySQL.)
CREATE DATABASE mysqltest;
USE mysqltest;
CREATE TABLE mysqltest.articles (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
body MEDIUMTEXT,
PRIMARY KEY (id))
ENGINE = MyISAM;
2. Speed
MySQL is fast. Really fast. Under normal conditions, benchmarks show that MyISAM tables beat every other general-purpose database and uses fewer system resources.
3. Full-text searching
Consider our table above. What if we need to search for keywords in both the title and the body? An easy solution is to add a fulltext index:
ALTER TABLE mysqltest.articles ADD FULLTEXT alltext (title, body);
SELECT * FROM mysqltest.articles
WHERE MATCH(title, body) AGAINST ('database article');
SELECT *, MATCH(title, body) AGAINST ('database article') AS rel
FROM mysqltest.articles
ORDER BY rel DESC;The disadvantages of MyISAM tables
There are several situations when MyISAM may not be suitable.1. Poor data integrity
MyISAM tables do not support transactions or foreign key constraints.
Consider a banking application where you are transferring money. This would typically involve two SQL UPDATE statements; a debit from one account and a credit to another. If MySQL or the server failed at the wrong point, you could end up with money in both accounts or neither.
2. Poor crash recovery
MySQL is reliable, but MyISAM tables can become corrupt following a crash. The problem can usually be fixed using REPAIR TABLE but it is an additional administrative overhead.
3. Table locking
MyISAM uses table-level locking. When a row is inserted or updated, all other changes to that table are held up until that request has been completed.
It is not always easy to prove whether this will cause problems for your web application but, in general, if you are performing more inserts/updates than selects, MyISAM may not be the best table type to choose.
Should you use MyISAM?
MyISAM is an ideal choice if you are new to MySQL, your web application is simple, has to be fast, or use full-text searches. However, it should not be used when data integrity is a priority. Possible applications could include content management systems, online bookmarking tools, RSS readers, or simple search engines.Note that you can mix table types within the same database. In practice, it may cause less developer confusion if you stick with a single table type.
See also:
- MySQL: the Pros and Cons of InnoDB Tables
- How to Install Apache
- How to Install PHP
- How to Install MySQL
- How to Use MySQL Foreign Keys for Quicker Database Development
MySQL: the Pros and Cons of InnoDB Tables
Following on from our look at MyISAM tables, today we examine InnoDB tables.InnoDB is less well known than the default MyISAM table type. However, there are several reasons why it could be better choice for your web application.
1. Data integrity and foreign key constraints
Foreign keys establish a relationship between columns in one table and those in another. For example, you might create a library application where books can be loaned to members. A foreign key constraint would ensure that a member existed before a book could be checked-out. Similarly, removing a user would not be possible until all their books were returned.
For further information about foreign keys and automating your database, refer to How to Use MySQL Foreign Keys for Quicker Database Development.
2. Transactions
InnoDB tables support transactions. A transaction allows multiple SQL commands to be treated as a single and reliable unit.
Consider a banking application where you are transferring money from one account to another. The transaction would only be committed if both accounts were altered successfully. If anything failed, the database would be rolled-back to a previous state.
In addition, InnoDB tables recover well from crashes. MySQL will analyze the log files to ensure the data is accurate so there is no need to repair tables.
3. Row-level locking
InnoDB uses row-level rather than table-level locking. If a row is being inserted, updated or deleted, only changes to the same row are held up until that request has completed. Tables that receive more updates than selects may be faster with InnoDB.
The disadvantages of InnoDB tables
There are a number of situations when InnoDB may not be suitable.1. Increased complexity
Creating an InnoDB table is no more complex than MyISAM, e.g.
CREATE TABLE employee (
id smallint(5) unsigned NOT NULL,
firstname varchar(30),
lastname varchar(30),
PRIMARY KEY (id)
) ENGINE=InnoDB;2. No full-text search
InnoDB tables do not support full-text searches; it is not easy to match one or more keywords against multiple columns.
3. Slower performance
If your application is primarily selecting data and performance is a priority, MyISAM tables will normally be faster and use fewer system resources.
Should you use InnoDB?
InnoDB is the best option if you need to create a reliable data-driven web application. In many ways, InnoDB is a better default choice than MyISAM:- unless you have a significantly large or heavily-used system, the speed differences are likely to be negligible
- full-text searches can be implemented in other ways, e.g. more complex SQL or server-side search algorithms.
Note that you can mix table types within the same database. In practice, it may cause less developer confusion if you stick with a single table type.
REFERENCES
http://blogs.sitepoint.com/mysql-myisam-table-pros-con/
