DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a ) ENGINE= PARTITION BY HASH(a) PARTITIONS 2; INSERT INTO t1 (a) VALUES (1),(2),(3),(2); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 # # # # # # # DROP TABLE t1; CREATE TABLE t1 (a ) ENGINE= PARTITION BY KEY(a) PARTITIONS 2; INSERT INTO t1 (a) VALUES ('a'),('b'),('c'); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 'b'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 # # # # # # # DROP TABLE t1; CREATE TABLE t1 (a , (a)) ENGINE= PARTITION BY KEY(a) PARTITIONS 2; SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 1 a 1 a # # NULL NULL # # INSERT INTO t1 (a) VALUES (1),(2),(3),(5); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a IN (1,3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 # # # # # # # DROP TABLE t1; CREATE TABLE t1 (a PRIMARY KEY) ENGINE= PARTITION BY KEY() PARTITIONS 2; SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 a # # NULL NULL # # INSERT INTO t1 (a) VALUES (1),(200),(3),(2); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1 # # # # # # # DROP TABLE t1; CREATE TABLE t1 (a ) ENGINE= PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (1000) ); INSERT INTO t1 (a) VALUES (1),(2),(400); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0,p1 # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 2; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p0 # # # # # # # INSERT INTO t1 (a) VALUES (10000); ERROR HY000: Table has no partition for value 10000 DROP TABLE t1; CREATE TABLE t1 (a ) ENGINE= PARTITION BY LIST(a) ( PARTITION abc VALUES IN (1,2,3), PARTITION def VALUES IN (100,101,102) ); INSERT INTO t1 (a) VALUES (1),(101),(1); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 abc,def # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL # # # # # # # INSERT INTO t1 (a) VALUES (50); ERROR HY000: Table has no partition for value 50 DROP TABLE t1; CREATE TABLE t1 (a , b ) ENGINE= PARTITION BY LIST(a) SUBPARTITION by HASH(b) ( PARTITION abc VALUES IN (1,2,3), PARTITION def VALUES IN (100,101,102) ); SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment INSERT INTO t1 (a,b) VALUES (1,1),(101,2),(1,3); EXPLAIN PARTITIONS SELECT a FROM t1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 abc_abcsp0,def_defsp0 # # # # # # # EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL # # # # # # # SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1'; TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_METHOD SUBPARTITION_METHOD test t1 abc abcsp0 LIST HASH test t1 def defsp0 LIST HASH SELECT * FROM INFORMATION_SCHEMA.PARTITIONS; DROP TABLE t1;