Databases Reference
In-Depth Information
Summary
Good schema design is pretty universal, but of course MySQL has special implemen-
tation details to consider. In a nutshell, it's a good idea to keep things as small and
simple as you can. MySQL likes simplicity, and so will the people who have to work
with your database:
• Try to avoid extremes in your design, such as a schema that will force enormously
complex queries, or tables with oodles and oodles of columns. (An oodle is some-
where between a scad and a gazillion.)
• Use small, simple, appropriate data types, and avoid
NULL
unless it's actually the
right way to model your data's reality.
• Try to use the same data types to store similar or related values, especially if they'll
be used in a join condition.
• Watch out for variable-length strings, which might cause pessimistic full-length
memory allocation for temporary tables and sorting.
• Try to use integers for identifiers if you can.
• Avoid the legacy MySQL-isms such as specifying precisions for floating-point
numbers or display widths for integers.
• Be careful with
ENUM
and
SET
. They're handy, but they can be abused, and they're
tricky sometimes.
BIT
is best avoided.
Normalization is good, but denormalization (duplication of data, in most cases) is
sometimes actually necessary and beneficial. We'll see more examples of that in the
next chapter. And precomputing, caching, or generating summary tables can also be a
big win. Justin Swanhart's Flexviews tool can help maintain summary tables.
Finally,
ALTER TABLE
can be painful because in most cases, it locks and rebuilds the
whole table. We showed a number of workarounds for specific cases; for the general
case, you'll have to use other techniques, such as performing the
ALTER
on a replica and
then promoting it to master. There's more about this later in the topic.
