jeudi 16 juin 2016

Partition MySQL table by Column Value

I have a MySQL table with 20 million rows. I want to partition to boost speed. The table is in the following format:

column    column   column   sector

data      data     data     Capital Goods
data      data     data     Transportation
data      data     data     Technology
data      data     data     Technology
data      data     data     Capital Goods
data      data     data     Finance
data      data     data     Finance

I have applied partitions using the following code:

ALTER TABLE technical
PARTITION BY LIST COLUMNS (sector)
(
PARTITION P1 VALUES IN ('Capital Goods'),
PARTITION P2 VALUES IN ('Health Care'),
PARTITION P3 VALUES IN ('Transportation'),
PARTITION P4 VALUES IN ('Finance'),
PARTITION P5 VALUES IN ('Technology'),
PARTITION P6 VALUES IN ('Consumer Services'),
PARTITION P7 VALUES IN ('Energy'),
PARTITION P8 VALUES IN ('Healthcare'),
PARTITION P9 VALUES IN ('Consumer Non-Durables'),
PARTITION P10 VALUES IN ('Consumer Durables')
);

Its all fine so far, but when I look at the table through phpMyAdmin it displays this: phpMyAdmin MySQL partitions

How can sector be less than capital goods ?

What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire