Database Reference
In-Depth Information
Here are several engines to be aware of:
■
InnoDB
is a transaction-safe engine (see Chapter 26, “Managing
Transaction Processing”). It does not support full-text searching.
■
MEMORY
is functionally equivalent to
MyISAM
, but as data is stored in
memory (instead of on disk) it is extremely fast (and ideally suited for
temporary tables).
■
MyISAM
is a high-performance engine. It supports full-text searching
(see Chapter 18, “Full-Text Searching”), but does not support transac-
tional processing.
■
ARIA
(specified as
ENGINE=Aria
) is a new transaction-safe engine that
also supports full-text searching and vital crash recovery features.
Engine types may be mixed, so within a single database you can have different
tables using different engines if required.
Caution
Foreign Keys Can't Span Engines There is one big downside to mixing engine types.
Foreign keys (used to enforce referential integrity, as explained in Chapter 1) cannot
span engines. That is, a table using one engine cannot have a foreign key referring to a
table that uses another engine.
So, which should you use? Well, that depends on what features you need.
ARIA
is new to MariaDB and provides the ideal combination of performance
and features. But, if you do need to use features in other engines, know that
the option of doing so is available to you.
To update table
definitions, the
ALTER TABLE
statement is used. But, ideally,
tables should never be altered after they contain data. You should spend suffi-
cient time anticipating future needs during the table design process so extensive
changes are not required later on.
To change a table using
ALTER TABLE
, you must specify the following infor-
mation:
■
The name of the table to be altered after the keywords
ALTER TABLE
.
(The table must exist or an error will be generated.)
■
The list of changes to be made.