Databases Reference
In-Depth Information
Crash recovery
If you have a lot of data, you should seriously consider how long it will take to
recover from a crash. MyISAM tables become corrupt more easily and take much
longer to recover than InnoDB tables. In fact, this is one of the most important
reasons why a lot of people use InnoDB when they don't need transactions.
Special features
Finally, you sometimes find that an application relies on particular features or op-
timizations that only some of MySQL's storage engines provide. For example, a
lot of applications rely on clustered index optimizations. On the other hand, only
MyISAM supports geospatial search inside MySQL. If a storage engine meets one
or more critical requirements, but not others, you need to either compromise or
find a clever design solution. You can often get what you need from a storage engine
that seemingly doesn't support your requirements.
You don't need to decide right now. There's a lot of material on each storage engine's
strengths and weaknesses in the rest of the topic, and lots of architecture and design
tips as well. In general, there are probably more options than you realize yet, and it
might help to come back to this question after reading more. If you're not sure, just
stick with InnoDB. It's a safe default and there's no reason to choose anything else if
you don't know yet what you need.
These topics might seem rather abstract without some sort of real-world context, so
let's consider some common database applications. We'll look at a variety of tables and
determine which engine best matches with each table's needs. We give a summary of
the options in the next section.
Logging
Suppose you want to use MySQL to log a record of every telephone call from a central
telephone switch in real time. Or maybe you've installed
mod_log_sql
for Apache, so
you can log all visits to your website directly in a table. In such an application, speed
is probably the most important goal; you don't want the database to be the bottleneck.
The MyISAM and Archive storage engines would work very well because they have
very low overhead and can insert thousands of records per second.
Things will get interesting, however, if you decide it's time to start running reports to
summarize the data you've logged. Depending on the queries you use, there's a good
chance that gathering data for the report will significantly slow the process of inserting
records. What can you do?
One solution is to use MySQL's built-in replication feature to clone the data onto a
second server, and then run your time- and CPU-intensive queries against the data on
the replica. This leaves the master free to insert records and lets you run any query you
want on the replica without worrying about how it might affect the real-time logging.
