CODEWITHSUNDEEP
mysql
joHN
joHN

Reputation: 1795

Mysql partition by key shows only 1 partition

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?

Upvotes: 3

Views: 307

Answers (1)

PerroVerd
PerroVerd

Reputation: 955

You are missing the number of partitions, after the partition by key put something like that (for 5 partitions)

PARTITION BY KEY (category)
PARTITIONS 5;

Are you using the best strategy with partitions? depends.

You have a lot of categories with similar number of products and also there are new categories added frequently. BY KEY or BY HASH are good options, with this options the products will distribute randomly over the number of partitions

Also with this kind of partition you dont have the warranty that inserting two products with diferent categories they go inside diferent partitions (the hash key could be the same)

You have a fixed number of categories, with heavy diferences between the numer of products in each category. Probably BY LIST is your friend, you select which category goes inside every partition.

Do you have a relative small number of products or you need to mix products from several categories in queries? Probably you dont need a partition.

Upvotes: 2

Related Questions