Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sql - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The problem is simply phpMyAdmin reresenting the data in an unusual way. The database is now working as expected and queries such as:

SELECT * WHERE ... AND sector = "Energy" 

are running much faster.

Thanks to Drew, Uueerdo, Rick James and everybody else for helping me understand this problem.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...