Partitioning in MySQL

-- Range partitioning
CREATE TABLE orders (
  id INT,
  created DATE,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(created)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- List partitioning
CREATE TABLE users_by_region (
  id INT,
  region VARCHAR(10)
)
PARTITION BY LIST(region) (
  PARTITION p_us VALUES IN ('US', 'CA'),
  PARTITION p_eu VALUES IN ('UK', 'DE', 'FR'),
  PARTITION p_asia VALUES IN ('JP', 'CN', 'IN')
);

-- DETAILED_TAB:
-- Hash partitioning
CREATE TABLE user_sessions (
  id INT,
  user_id INT,
  data TEXT
)
PARTITION BY HASH(user_id)
PARTITIONS 10;

-- Manage partitions
ALTER TABLE orders ADD PARTITION (
  PARTITION p2025 VALUES LESS THAN (2026)
);

ALTER TABLE orders DROP PARTITION p2022;

-- Query specific partition
SELECT * FROM orders PARTITION (p2024)
WHERE created >= '2024-01-01';

-- Check partition info
SELECT table_name, partition_name, table_rows
FROM information_schema.partitions
WHERE table_schema = 'your_db';