Partition MySQL table by Column Value

Question

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:

How can sector be less than capital goods ?

What am I doing wrong?

I requested for Rick to have a look, Ned. You also need to give a lot more detail to schemas and access paths (UX and what they do) for anyone to help. — Drew, Jun 14 '16 at 18:18
What is the data type of `sector`? What version of MySQL Server? How does the SHOW CREATE reflect your partitions? — Uueerdo, Jun 14 '16 at 18:19
@Uueerdo 'sector' is a string and MySQL is version 5.5. I am not sure what the third part of your question means — Ned Hulton, Jun 14 '16 at 18:27
http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html should return a create statement reflecting the table's current structure; I am not positive it includes partitions (it omits triggers), but it is worth try to see if it is just phpMyAdmin misrepresenting, or MySQL interpreting your alter differently than you intented. — Uueerdo, Jun 14 '16 at 18:31
I think the language disconnect is evident whereas Uu and I would talk in terms of schemas, and lower level access, your experience is with phpmyadmin and strings. So different worlds more or less. Might be hard to bridge that divide easily. Not sure if others saw yesterday's [question](http://stackoverflow.com/q/37800990) ... as such maybe this one isn't needed. It is not like we forgot about it. — Drew, Jun 14 '16 at 18:31
I have never seen a case where `PARTITION BY LIST` will improve performance. Please provide `SHOW CREATE TABLE` and the `SELECT` that you want to run faster. — Rick James, Jun 15 '16 at 07:51

score 0 · Accepted Answer · answered Jun 15 '16 at 23:38

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.

Partition MySQL table by Column Value

1 Answers1

Linked