パーティションプルーニングと呼ばれる最適化は、「「一致する値がない可能性があるパーティションをスキャンしません」」と記述できる比較的単純な概念に基づいています。 次のステートメントによってパーティションテーブル t1 が作成されるとします:

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

次のような SELECT ステートメントから結果を取得するとします:

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

返される必要がある行がパーティション p0 または p3 のいずれにもないことを簡単に確認できます。つまり、一致する行を検索するには、パーティション p1 および p2 でのみ検索が必要です。 検索を制限することで、テーブル内のすべてのパーティションをスキャンするよりも、一致する行の検索にかかる時間と労力を大幅に削減できます。 必要のないパーティションをこのように「省く」ことをプルーニングといいます。 オプティマイザがこのクエリーの実行でパーティションプルーニングを使用できるときは、同じカラム定義およびデータが含まれているパーティション化されていないテーブルで同じクエリーを実行するよりも、速度が大幅に向上することがあります。

WHERE 条件を次の 2 つのケースのいずれかにまとめられるとき、オプティマイザはプルーニングを実行できます。

最初のケースで、オプティマイザは指定された値についてパーティショニング式を単純に評価し、どのパーティションに値が含まれるかを判別して、このパーティションのみをスキャンします。 多くの場合、この等号を別の算術比較 (<、>、<=、>=、および <> を含む) に置き換えることができます。 WHERE 句で BETWEEN を使用するクエリーも、パーティションプルーニングの利点を活用できます。 このセクションの後続の例を参照してください。

2 番目のケースで、オプティマイザはリスト内の各値についてパーティショニング式を評価して、一致するパーティションのリストを作成してから、このパーティションリストのパーティションのみをスキャンします。

SELECT、DELETE および UPDATE ステートメントでは、パーティションプルーニングがサポートされます。 INSERT ステートメントは、挿入された行ごとに 1 つのパーティションにのみアクセスします。これは、現在 EXPLAIN の出力には表示されていませんが、HASH または KEY によってパーティション化されたテーブルにも当てはまります。

短い範囲にもプルーニングを適用できます (オプティマイザが同等の値リストに変換できるもの)。 たとえば、前の例では、WHERE 句を WHERE region_code IN (126, 127, 128, 129) に変換できます。 次に、オプティマイザは、リスト内の最初の 2 つの値がパーティション p1 にあること、パーティション p2 内の残りの 2 つの値があること、および他のパーティションに関連する値が含まれていないことを判断できるため、一致する行を検索する必要はありません。

オプティマイザは、RANGE COLUMNS または LIST COLUMNS パーティション化を使用するテーブルの複数のカラムに対する前述の型の比較を含む WHERE 条件のプルーニングも実行できます。

このタイプの最適化は、パーティショニング式が同一性や範囲で構成されていてそれを同一性のセットにまとめられるとき、またはパーティショニング式が増減する関係を表すときに適用できます。 パーティショニング式が YEAR() または TO_DAYS() 関数を使用するとき、DATE カラムまたは DATETIME カラムでパーティション化されるテーブルにプルーニングを適用することもできます。 パーティション化式で TO_SECONDS() 関数を使用する場合は、このようなテーブルにプルーニングを適用することもできます。

DATE カラムでパーティション化されたテーブル t2 が、次に示すステートメントを使用して作成されるとします:

CREATE TABLE t2 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION d0 VALUES LESS THAN (1970),
    PARTITION d1 VALUES LESS THAN (1975),
    PARTITION d2 VALUES LESS THAN (1980),
    PARTITION d3 VALUES LESS THAN (1985),
    PARTITION d4 VALUES LESS THAN (1990),
    PARTITION d5 VALUES LESS THAN (2000),
    PARTITION d6 VALUES LESS THAN (2005),
    PARTITION d7 VALUES LESS THAN MAXVALUE
);

t2 を使用する次のステートメントでは、パーティションプルーニングを使用できます。

SELECT * FROM t2 WHERE dob = '1982-06-23';

UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

最後のステートメントの場合、オプティマイザは次のようにも動作できます。

ここまで、RANGE パーティショニングを使用する例のみを見てきましたが、プルーニングはほかのパーティショニングタイプにも適用できます。

次に示すテーブル t3 のように、パーティショニング式が増加または減少している LIST によってパーティション化されたテーブルがあるとします。 (この例では、簡単にするために region_code カラムが値 1 から 10 まで (両端を含む) に制限されると想定します)。

CREATE TABLE t3 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
    PARTITION r0 VALUES IN (1, 3),
    PARTITION r1 VALUES IN (2, 5, 8),
    PARTITION r2 VALUES IN (4, 9),
    PARTITION r3 VALUES IN (6, 7, 10)
);

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3 などのステートメントの場合、オプティマイザはどのパーティションで値 1、2、および 3 が見つかるかを判別して (r0 および r1)、残りのもの (r2 および r3) をスキップします。

HASH または [LINEAR] KEY によってパーティション化されたテーブルの場合も、パーティショニング式で使用されるカラムに対して WHERE 句が単純な = 関係を使用しているときは、パーティションプルーニングを適用できます。 次のように作成されたテーブルがあるとします。

CREATE TABLE t4 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;

カラム値を定数と比較するステートメントはプルーニングできます。

UPDATE t4 WHERE region_code = 7;

プルーニングは短い範囲にも適用できます。オプティマイザがそのような条件を IN 関係に変換できるためです。 たとえば、前に定義したものと同じテーブル t4 を使用して、次のようなクエリーをプルーニングできます。

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

どちらの場合も、WHERE 句はオプティマイザによって WHERE region_code IN (3, 4, 5) に変換されます。

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

HASH または [LINEAR] KEY によってパーティション化されたテーブルの場合、プルーニングを使用できるのは整数カラムに対してのみです。 たとえば、dob は DATE カラムであるため、次のステートメントにはプルーニングを使用できません。

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

ただし、このテーブルが年値を INT カラムに格納する場合は、WHERE year_col >= 2001 AND year_col <= 2005 を持つクエリーをプルーニングできます。

MySQL Cluster で使用される NDB ストレージエンジンなど、自動パーティション分割を提供するストレージエンジンを使用するテーブルは、明示的にパーティション化されている場合にプルーニングできます。