Database Management Systems | 99to need it.” Even transactions are not supported with the default storage engine
(MyISAM) to this day. In versions prior to 5.0, there were many bugs that would
silently discard incorrect data rather than raising an error. To be fair, new versions of
MySQL are addressing a lot of its issues. I would still recommend PostgreSQL as a
general rule where speed is not the primary criterion, since it has had enterprise-level
features for much longer. If you use MySQL, take these recommendations:
- Use version 5.0 or later. Many of the issues that existed with previous versions
have been fixed or improved in 5.0 and newer versions. - Use InnoDB for absolutely anything where data integrity or concurrency matter.
MyISAM, the default engine on most MySQL installations, does not support fea-
tures that most RDBMSs consider essential: foreign key constraints, row-level
locking, and transactions. In most business environments, these features are
non-negotiable. InnoDB is a journaled storage engine that is much more resil-
ient to failures. Rails does the right thing here and defaults to the InnoDB stor-
age engine when creating tables.
Unfortunately, InnoDB can be much slower than MyISAM, and the table sizes
are usually several times larger. MyISAM is usually faster when reads vastly out-
number writes or vice versa, while InnoDB is generally faster when reads and
writes are balanced. It all comes down to the requirements of the specific appli-
cation; these are general rules. You should always benchmark with your real
data, and an accurate sample of queries and statements you will be issuing, in a
realistic environment.
There are a few exceptions to this guideline: MyISAM may be a better choice if you
need full-text indexing (which is only supported on MyISAM tables at this time). In
addition, if raw speed of reads or writes is the primary concern, MyISAM can
help. For example, a logging server for web analytics might use MyISAM tables:
you want to be able to dump logs into it as fast as possible, and reads are per-
formed far less often than writes. - Set the SQL mode toTRADITIONAL. This can be accomplished with the following
command:
SET GLOBAL sql_mode='TRADITIONAL';
This will make MySQL a little bit more strict, raising errors on incorrect data
rather than silently discarding it.
MySQL does have some clear advantages over PostgreSQL in some situations. On
the whole, MySQL tends to be faster. For many web applications, query speed may
be the most important factor. MySQL also has more stable, tested replication and
clustering options available. MySQL is also somewhat better at handling binary data
stored in the database (we discuss this at length later in the chapter). For many web
applications, MySQL may be a clear win.