Boost MySQL Performance: A Simple Guide to Database Partitioning by List

If you have a big table for data warehouse, when you need to make a report it would take lot of time to compute. What if we can partition the same table to store separtion partition base on specific location, so that we got a smaller section to work on.

In this example I use "export_product_detail" table and link with countries as following:
First, we just have a little countries table. Simple stuff, just our list of nations.

CREATE TABLE countries (
    country_id INT PRIMARY KEY,
    country_name VARCHAR(50) UNIQUE
);

INSERT INTO countries VALUES
(1, 'Cambodia'), (2, 'Thailand'), (3, 'Laos'), (4, 'Vietnam'), (5, 'Other');

Then, for the big export_product_detail table, we tell it to sort itself by country_id. This is the magic part: PARTITION BY LIST.

CREATE TABLE export_product_detail (
    export_id INT,
    product_id INT,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    country_id INT NOT NULL,
    export_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (export_id, product_id, country_id),
)
PARTITION BY LIST (country_id) (
    PARTITION p_cambodia VALUES IN (1),
    PARTITION p_thailand VALUES IN (2),
    PARTITION p_laos VALUES IN (3),
    PARTITION p_vietnam VALUES IN (4),
    PARTITION p_other VALUES IN (5)
);

-- And some extra indexes for speed!
CREATE INDEX idx_export_date ON export_product_detail (export_date);
CREATE INDEX idx_product_id ON export_product_detail (product_id);

Learn How SQL Server Partitioning Made Simple: Step-by-Step for Beginners

Full Demo on How to do Table Partitioning by List in MySQL step by step

Learn More Introduction to Database (SQL) in Khmer with videos: Chapter 1 | Chapter 2 | Chapter 3

Why is this awesome?

-Super Fast Searches: If I need data from Cambodia, the database only checks the 'Cambodia' drawer. No more rummaging through the whole closet!
-Easier to Manage: If one "drawer" gets too full, it's easier to deal with just that section.

Dowload Example Data with 3500+ records

Query Examples (explain detail in video)

What if a New Country Joins the Party? (Like USA!)

So, we start exporting to the USA. My boss asks, "Can we get a USA drawer?" Absolutely!

First, add USA to our countries list (let's say country_id = 6):

INSERT INTO countries (country_id, country_name) VALUES (6, 'USA');

Then, tell the main table to make a new drawer for country_id = 6:

ALTER TABLE export_product_detail
ADD PARTITION (PARTITION p_usa VALUES IN (6));

And just like that, boom! A new 'USA' drawer is ready. If you want to know how to verify the new partition watch the whole video.

Free Khmer Ebook Download (PDF): Database | Microsoft Access | Python Programming