-- ❌ Error: A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    userid BIGINT
) PARTITION BY HASH(userid) PARTITIONS 16;

-- ✅ Correct: The primary key includes the partition key
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT,
    userid BIGINT NOT NULL,
    PRIMARY KEY (userid)
) PARTITION BY HASH(userid) PARTITIONS 16;

-- ❌ Write hotspot: new data is always written to the last partition
CREATE TABLE t (
    id BIGINT AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (200000),
    PARTITION p_maxvalue VALUES LESS THAN MAXVALUE   -- All new data resides here
);


-- ✅ RANGE manages the lifecycle by time + HASH distributes writes
) PARTITION BY RANGE COLUMNS(created_at)
  SUBPARTITION BY HASH(userid) SUBPARTITIONS 6 (
    ...
);

-- ✅ Accesses only one node, with millisecond-level response
SELECT * FROM orders WHERE userid = 12345;

-- ❌ Accesses all nodes, with each node being queried once
SELECT * FROM orders WHERE orderno = 'ORD-001';

CREATE TABLE orders (
    ...
    KEY idx_order_no (orderno)     -- A LOCAL index that does not include the partition key userid
) PARTITION BY HASH(userid) PARTITIONS 16;

SELECT * FROM orders WHERE orderno = 'ORD-2026-001';

-- From this
SELECT * FROM orders WHERE orderno = 'ORD-001';
-- Change to this (if the business logic can obtain the userid)
SELECT * FROM orders WHERE userid = 123 AND orderno = 'ORD-001';

-- Orders table, before transformation (MySQL)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    userid BIGINT,
    orderno VARCHAR(64),
    amount DECIMAL(10,2),
    status TINYINT,
    created_at DATETIME,
    KEY idx_order_no (orderno),
    KEY idx_created (created_at)
);

-- Orders table, after transformation (TDSQL Boundless)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    userid BIGINT NOT NULL,
    orderno VARCHAR(64) NOT NULL,
    amount DECIMAL(10,2),
    status TINYINT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (userid, orderno),             -- The partition key is the first column in the primary key
    KEY idx_order_no (orderno),                -- The order number index handles searches based on order number.
    KEY idx_user_created (userid, created_at)  -- For user order list queries
) PARTITION BY HASH(userid) PARTITIONS 16;

--  ✅ No modification required; it naturally hits the partition.
SELECT * FROM orders WHERE userid = 123 ORDER BY created_at DESC LIMIT 20;
SELECT * FROM orders WHERE userid = 123 ORDER BY orderno DESC LIMIT 20;

-- Optimal scenario: Query the order number with the user's userid.
SELECT * FROM orders WHERE userid = 123 AND orderno = 'ORD-001';
-- Acceptable (LOCAL index, 16 index lookups)
SELECT * FROM orders WHERE orderno = 'ORD-001';

CREATE TABLE dim_city (
    city_id INT PRIMARY KEY,
    city_name VARCHAR(64)
) sync_level = node(all) distribution = node(all);

-- With partition key
EXPLAIN SELECT * FROM orders WHERE userid = 12345;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: p1
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
        
-- Without partition key        
orders
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 258
          ref: const
         rows: 9
     filtered: 100.00
        Extra: NULL