PartitioningΒΆ

The optimizer is able to perform partition pruning. This means that it is capable of analyzing an incoming query, comparing it to dictionary information, and then only accessing only the required table partitions.

You can think of partitioning as similar to a view - in that it is a logical representation of one table, with a set of tables below it. Partitioning is suitable over indexing in cases where all of the queries follow a common pattern. For example:

  • Soft Delete. Many applications implement a soft-delete (i.e. is_deleted) and frequently only access either the deleted or the undeleted data.
  • Multi-versioned schema [1]. Some applications have a policy of never deleting data, keeping older generations of rows around for historical purposes. For example, you update a user’s address but keep all previous addresses. Partitioning by whether an address is expired can help improve memory.
  • Time oriented. For example, partitioning invoices by quarter or financial cycle.
  • Locality. For example, partitioning invoices by store_id or region.
Partition Pruning

Partitioning performance is best when most of the queries only need to access one or a small subset of partitions at a time. If we consider our example schema, it might be the case that all queries to the CountryLanguage table are for officially supported languages only. If this is true, partitioning by isOfficial can be done as follows:

# IsOfficial is an ENUM, but can be converted to a CHAR at the same space
# ENUM types only support KEY partitioning
# The partitioning key must be part of the primary and all unique keys

ALTER TABLE CountryLanguage MODIFY IsOfficial CHAR(1) NOT NULL DEFAULT 'F', DROP PRIMARY KEY, ADD PRIMARY KEY(CountryCode, Language, IsOfficial);
ALTER TABLE CountryLanguage PARTITION BY LIST COLUMNS (IsOfficial) (
 PARTITION pUnofficial VALUES IN ('F'),
 PARTITION pOfficial VALUES IN ('T')
);

Example 32: A partitioned table is pruned by the optimizer

EXPLAIN FORMAT=JSON
SELECT * FROM CountryLanguage WHERE isOfficial='T' AND CountryCode='CAN';
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.40"
    },
    "table": {
      "table_name": "CountryLanguage",
      "partitions": [                     # Only the partition containing official
        "pOfficial"                       # languages was accessed
      ],
      "access_type": "ref",
      "possible_keys": [
        "PRIMARY",
        "CountryCode"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "CountryCode"
      ],
      "key_length": "3",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 0,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "2.00",
        "eval_cost": "0.04",
        "prefix_cost": "2.40",
        "data_read_per_join": "8"
      },
      "used_columns": [
        "CountryCode",
        "Language",
        "IsOfficial",
        "Percentage"
      ],
      "attached_condition": "(`world`.`CountryLanguage`.`IsOfficial` = 'T')"
    }
  }
}

Tip

In addition to partition pruning, MySQL supports syntax to explicitly target only a select number of partitions. This can be used by applications in a similar way to query hints: SELECT * FROM CountryLanguage PARTITION (pOfficial) WHERE CountryCode='CAN';

[1]In data warehousing this is called slowly changing dimensions https://en.wikipedia.org/wiki/Slowly_changing_dimension