We’re not trying to write an exhaustive list; our goal is just to
present some key factors that are relevant to schema design.
The MyISAM Storage Engine
Table locks
MyISAM tables have table-level locks. Be careful this doesn’t become a
bottleneck.
No automated data recovery
If the MySQL server crashes or power goes down, you should check and possi-
bly repair your MyISAM tables before using them. If you have large tables, this
could take hours.
No transactions
MyISAM tables don’t support transactions. In fact, MyISAM doesn’t even guar-
antee that a single statement will complete; if there’s an error halfway through a
multirow UPDATE, for example, some of the rows will be updated and some
won’t.
Only indexes are cached in memory
MyISAM caches only the index inside the MySQL process, in the key buffer. The
operating system caches the table’s data, so in MySQL 5.0 an expensive operat-
ing system call is required to retrieve it.
Compact storage
Rows are stored jam-packed one after another, so you get a small disk footprint
and fast full table scans for on-disk data.
The Memory Storage Engine
Table locks
Like MyISAM tables, Memory tables have table locks. This isn’t usually a prob-
lem though, because queries on Memory tables are normally fast.
No dynamic rows
Memory tables don’t support dynamic (i.e., variable-length) rows, so they don’t
support BLOB and TEXT fields at all. Even a VARCHAR(5000) turns into a
CHAR(5000)—a huge memory waste if most values are small.
Hash indexes are the default index type
Unlike for other storage engines, the default index type is hash if you don’t spec-
ify it explicitly.
No index statistics
Memory tables don’t support index statistics, so you may get bad execution
plans for some complex queries.
Content is lost on restart
Memory tables don’t persist any data to disk, so the data is lost when the server
restarts, even though the tables’ definitions remain.
The InnoDB Storage Engine
Transactional
InnoDB supports transactions and four transaction isolation levels.
Foreign keys
As of MySQL 5.0, InnoDB is the only stock storage engine that supports foreign
keys. Other storage engines will accept them in CREATE TABLE statements, but
won’t enforce them. Some third-party engines, such as solidDB for MySQL and
PBXT, support them at the storage engine level too; MySQL AB plans to add
support at the server level in the future.
Row-level locks
Locks are set at the row level, with no escalation and nonblocking selects—stan-
dard selects don’t set any locks at all, which gives very good concurrency.
Multiversioning
InnoDB uses multiversion concurrency control, so by default your selects may
read stale data. In fact, its MVCC architecture adds a lot of complexity and pos-
sibly unexpected behaviors. You should read the InnoDB manual thoroughly if
you use InnoDB.
Clustering by primary key
All InnoDB tables are clustered by the primary key, which you can use to your
advantage in schema design.
All indexes contain the primary key columns
Indexes refer to the rows by the primary key, so if you don’t keep your primary
key short, the indexes will grow very large.
Optimized caching
InnoDB caches both data and memory in the buffer pool. It also automatically
builds hash indexes to speed up row retrieval.
Unpacked indexes
Indexes are not packed with prefix compression, so they can be much larger
than for MyISAM tables.
Slow data load
As of MySQL 5.0, InnoDB does not specially optimize data load operations. It
builds indexes a row at a time, instead of building them by sorting. This may
result in significantly slower data loads.
Blocking AUTO_INCREMENT
In versions earlier than MySQL 5.1, InnoDB uses a table-level lock to generate
each new AUTO_INCREMENT value.
No cached COUNT(*) value
Unlike MyISAM or Memory tables, InnoDB tables don’t store the number of
rows in the table, which means COUNT(*) queries without a WHERE clause can’t be
optimized away and require full table or index scans. See“Optimizing COUNT( )
Queries” on page 188 for more on this topic.
