3

I have a MySQL table with 20 million rows. Querying this database is taking too much time. The database is in the format below:

Column    Column   Column   Sector

data      data     data     Retail
data      data     data     Utility
data      data     data     Retail
data      data     data     Insurance
data      data     data     Retail
data      data     data     Agriculture
data      data     data     Agriculture
data      data     data     Retail

I want to be able to partition the database by sector. This should boost speed for queries where the sector is specified. I have tried the following and it does not work. Where am I going wrong?

Alter table 'technical' partition by values in `sector`
Ned Hulton
  • 477
  • 3
  • 12
  • 27
  • 1
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html – Gordon Linoff Jun 14 '16 at 01:42
  • @GordonLinoff I have looked at that already, but as a beginner I am unsure which to use. – Ned Hulton Jun 14 '16 at 01:45
  • 1
    Since your table already contains data.. your alter statement will not work.. It is better to create partition when you are creating the table it self.. One of the simpler steps is Step 1: Create a new partitioned table from the old table using syntax including the partitioning clause Step 2: Drop or rename the old table Step 3: Rename the table created in step 1 to the name of dropped table. – Indiecoder Jun 14 '16 at 03:09

1 Answers1

5

https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html

From those Partitioning Types, I think LIST COLUMNS partitioning would be the best choice for you. Read further details about it from here ..

https://dev.mysql.com/doc/refman/5.7/en/partitioning-columns-list.html

Your code should look like ..

ALTER TABLE technical
PARTITION BY LIST COLUMNS (sector)
  (
    PARTITION p01 VALUES IN ('Retail'),
    PARTITION p02 VALUES IN ('Utility'),
    PARTITION p03 VALUES IN ('Insurance'),
    PARTITION p04 VALUES IN ('Agriculture')
  );
Muhammad Abdul Arif Sarker
  • 1,211
  • 4
  • 15
  • 25
  • Hi Arif, thanks for the great answer. If you have a few minutes spare please have a look at this follow-up question: http://stackoverflow.com/questions/37818604/partition-mysql-table-by-column-value – Ned Hulton Jun 14 '16 at 17:39
  • 2
    Any way to make uncategorized partition, for any non listed values? – Hitesh Dec 16 '17 at 07:28