Mysql partition by key shows only 1 partition

Viewed 250

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?

1 Answers
Related