Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Partitioned Tables

Explore how to create partitioned reporting tables in MySQL using list partitioning on string columns like PaymentMethod. Understand table structures, composite keys, and data insertion techniques to optimize query performance and maintain organized datasets. Gain practical skills in inspecting metadata and implementing alternate partitioning strategies to handle real-world reporting needs.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
/* Create the reporting table with LIST COLUMNS partitioning */
DROP TABLE IF EXISTS OrdersList;
CREATE TABLE OrdersList (
    OrderID       INT NOT NULL,
    CustomerID    INT NOT NULL,
    OrderDate     DATE NOT NULL,
    PaymentMethod VARCHAR(20) NOT NULL,
    TotalAmount   DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (OrderID, PaymentMethod)
)
PARTITION BY LIST COLUMNS (PaymentMethod) (
    PARTITION p_cc  VALUES IN ('Credit Card'),
    PARTITION p_pp  VALUES IN ('PayPal'),
    PARTITION p_cod VALUES IN ('Cash on Delivery')
);
/* Populate the reporting table */
INSERT INTO OrdersList (OrderID, CustomerID, OrderDate, PaymentMethod, TotalAmount)
SELECT OrderID, CustomerID, OrderDate, PaymentMethod, TotalAmount
FROM   Orders;
/* Check partitions */
SELECT   PaymentMethod,
         COUNT(*) AS OrderCount
FROM     OrdersList
GROUP BY PaymentMethod
ORDER BY PaymentMethod;

Explanation

The explanation of the solution code is given below:

  • Line 2: Remove the existing OrdersList table if it already exists. This ensures the script can be rerun without errors due to duplicate table names.

  • Lines 4–16: Create the reporting table and define list partitioning on the PaymentMethod column.

    • Lines 4–11: Define the table structure for OrdersList. The columns match the structure of the base Orders table so data can be copied safely:

      • OrderID, CustomerID, and OrderDate store order level attributes.

      • PaymentMethod is a string column used for partitioning.

      • TotalAmount stores the order total.

      • Line 10: Define a composite primary key using OrderID and PaymentMethod. Including the partitioning column in the primary key is required because MySQL requires all unique keys to include the partition key.

    • Lines 12–16: Apply PARTITION BY LIST COLUMNS (PaymentMethod) to distribute rows based on discrete payment method values.

      • p_cc stores rows ...