Horizontal partitioning in MySQL is the technique of splitting a table by rows, so that each partition contains a subset of the rows but all the columns. Logically it is still one table, but physically the data is distributed across multiple partitions (or even separate instances).

Horizontal partitioning is used when a table grows so large that scanning all rows becomes slow and costly.

How Horizontal Partitioning Works

  • Partitioning key:

    • A column such as date, user_id, order_id, or region is chosen to decide which partition a row belongs to.

  • Partition type:

    • Range: partitions based on ranges (for example, by month or year).

    • List: partitions based on a fixed set of values (for example, by region or country).

    • Hash: a hash function distributes rows evenly across partitions.

Each partition stores only the rows that match its rule, while the table definition and schema remain the same for all partitions.

Benefits of Horizontal Partitioning

  • Faster queries (partition pruning):

    • Queries that include the partitioning key in the WHERE clause can skip entire partitions, reading only the relevant ones.

  • Easier maintenance and cleanup:

    • Old or unused data can be dropped by removing or archiving specific partitions instead of deleting rows from the whole table.

  • Improved I/O and parallelism:

    • Different partitions can be scanned or updated in parallel, leveraging multiple disks or CPU cores.

Typical Use Cases

  • Very large tables (logs, orders, event data).

  • Time‑series data where old data is rarely accessed after a certain period.

  • Multi‑tenant or region‑based data that naturally groups by tenant or location.

For beginners, horizontal partitioning is like filing a big list into separate folders by date or category: the complete list is still one logical thing, but you only need to open the relevant folder when you search.

Summary

Horizontal partitioning in MySQL splits a table’s rows into smaller partitions based on a key, improving query performance and maintenance. By using partition pruning, the database reads only the necessary partitions instead of scanning the entire table, making horizontal partitioning ideal for large, high‑volume datasets.