MySQL® Administrator's Bible

Chapter 15. Partitioning

IN THIS CHAPTER

• Learning about partitioning

• Partitioning tables

• Using MERGE tables

• Partitioning with MySQL cluster

• Programmatic partitioning

Partitioning is dividing up data in a database into distinct independent elements. Partitioning has three main purposes: to provide data manageability, performance, and availability. Partitioning is not part of relational database theory; it is a practical detail to help queries perform better. There are several methods of implementing partitioning with MySQL.

Learning About Partitioning

There are two ways to partition database tables:

• Horizontal Partitioning — In horizontal partitioning, different rows are stored in different tables. Perhaps customers with IDs less than 500,000 are stored in customers1, while customers with IDs greater than or equal to 500,000 are stored in customers2. These two tables now horizontally partition the data set of customers. In horizontal partitioning, the table schemas are exactly the same (i.e., customers1 and customers2 have the same schema). It is as if a table were cut into parts using a horizontal line.

  Archiving older data is a commonly used example of horizontal partitioning. Another technique using horizontal partitioning is sharding, which involves using separate servers to host similar types of data. For example, customers with IDs less than 500,000 are stored in the customers table on server1, and customers with IDs greater than or equal to 500,000 are stored in the customers table ...