I have a scenario like I have a product table with different categories. So queries will be specific to category. So I thought if I can separate each category as each partition it will improve performance.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id int(11) AUTO_INCREMENT,
pname varchar(11) default '',
category char(10) default 'general',
PRIMARY KEY thisKey (id,category)
)
PARTITION BY KEY (category)
;
I loaded data from two categories. But its showing only one partition:
mysql> EXPLAIN PARTITIONS SELECT * FROM products\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
partitions: p0
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra:
Can somebody help me why its not having two partition, or I choose wrong partition type for this requirement?