Databases Reference
In-Depth Information
Summary
This chapter has been more of a potpourri of different topics than some of the previous
chapters were. We'll wrap up by revisiting some of the most important points from
each topic:
Partitioned tables
Partitioning is a kind of cheap, coarse indexing that works at large scale. For best
results, either forget about indexing and plan to full-scan selected partitions, or
make sure that only one partition is hot and it fits in memory, including its indexes.
Stick to about 150 or fewer partitions per table, watch out for subtleties that defeat
pruning, and monitor the per-row and per-query overhead of partitioning.
Views
Views can be useful for abstracting underlying tables and complex queries. Beware
of views that use temporary tables, though, because they don't push your
WHERE
clauses down to the underlying queries; nor do they have indexes themselves, so
you can't query them efficiently in a join. Using views as conveniences is probably
the best approach.
Foreign keys
Foreign key constraints push constraints into the server, where they can be more
efficient. However, they can also add complexity, extra indexing overhead, and
interactions between tables that cause more locking and contention. We think
foreign keys are a nice-to-have feature for ensuring system integrity, but they're a
luxury for applications that need extremely high performance; most people don't
use them when performance is a concern, preferring instead to trust the application
code.
Stored routines
MySQL's implementation of stored procedures, triggers, stored functions, and
events is quite frankly pretty unimpressive. There are also a lot of problems with
statement-based replication. Use these features when they can save you a lot of
network round-trips—in such cases, you can get much better performance by cut-
ting out costly latency. You can also use them for the usual reasons (centralizing
business logic, enforcing privileges, and so on), but this just doesn't work as well
in MySQL as it does in the bigger, more complex and mature database servers.
Prepared statements
Prepared statements are useful when a large portion of the cost of executing state-
ments is from transferring statements across the network, parsing the SQL, and
optimizing the SQL. If you'll repeat the same statement many times, you can save
on these costs by using prepared statements because they're parsed once, there is
some execution plan caching, and the binary protocol is more efficient than the
ordinary text-based protocol.
