Databases Reference
In-Depth Information
You can also run queries at times of low load, but don't rely on this strategy continuing
to work as your application grows.
Another option is to log to a table that contains the year and name or number of the
month in its name, such as
web_logs_2012_01
or
web_logs_2012_jan
. While you're busy
running queries against tables that are no longer being written to, your application can
log records to its current table uninterrupted.
Read-only or read-mostly tables
Tables that contain data used to construct a catalog or listing of some sort (jobs, auc-
tions, real estate, etc.) are usually read from far more often than they are written to.
This seemingly makes them good candidates for MyISAM—
if you don't mind what
happens when MyISAM crashes
. Don't underestimate how important this is; a lot of
users don't really understand how risky it is to use a storage engine that doesn't even
try to get their data written to disk. (MyISAM just writes the data to memory and
assumes the operating system will flush it to disk sometime later.)
It's an excellent idea to run a realistic load simulation on a test server
and then literally pull the power plug. The firsthand experience of re-
covering from a crash is priceless. It saves nasty surprises later.
Don't just believe the common “MyISAM is faster than InnoDB” folk wisdom. It is
not
categorically true. We can name dozens of situations where InnoDB leaves
MyISAM in the dust, especially for applications where clustered indexes are useful or
where the data fits in memory. As you read the rest of this topic, you'll get a sense of
which factors influence a storage engine's performance (data size, number of I/O op-
erations required, primary keys versus secondary indexes, etc.), and which of them
matter to your application.
When we design systems such as these, we use InnoDB. MyISAM might seem to work
okay in the beginning, but it will absolutely fall on its face when the application gets
busy. Everything will lock up, and you'll lose data when you have a server crash.
Order processing
When you deal with any sort of order processing, transactions are all but required.
Half-completed orders aren't going to endear customers to your service. Another im-
portant consideration is whether the engine needs to support foreign key constraints.
InnoDB is your best bet for order-processing applications.
