High Performance MySQL, 4th Edition

Chapter 6. Schema Design and Management

Good logical and physical design is the cornerstone of high performance, and you must design your schema for the specific queries you will run. This often involves trade-offs. For example, a denormalized schema can speed up some types of queries but slow down others. Adding counter and summary tables is a great way to optimize queries, but they can be expensive to maintain. MySQL’s particular features and implementation details influence this quite a bit.

Likewise, your schema will evolve over time—as a result of what you learn about how you store and access data as well as how your business requirements change over time. This means that you should plan for schema changes as a frequent event. Later in this chapter, we help guide you through how to keep this activity from becoming an operational bottleneck for your organization.

This chapter—and the following one, which focuses on indexing—cover the MySQL-specific bits of schema design. We assume that you know how to design databases, so this is not an introductory chapter, or even an advanced chapter, on database design. As a chapter on MySQL database design, it’s about what is different when designing databases with MySQL rather than other RDBMSs. If you need to study the basics of database design, we suggest Clare Churcher’s book Beginning Database Design (Apress).

This chapter is preparation for the two that follow. In these three chapters, we will explore the interaction of logical design, ...