Differences

This shows you the differences between two versions of the page.

--- partitioningtable [2025/03/28 12:01] – created jwan
+++ partitioningtable [2025/10/10 10:15] (current) – external edit 127.0.0.1
@@ Line 19: / Line 19: @@
  PARTITION `p020m` VALUES LESS THAN (2000000) ENGINE = MyISAM,
  PARTITION `p100m` VALUES LESS THAN MAXVALUE ENGINE = MyISAM);
+</code>
+Can also be good if partitioned by date/time:
+<code>
+    PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp)) (
+    PARTITION p_historic VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01')),
+    PARTITION p_2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01')),
+    PARTITION p_2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01')),
+    PARTITION p_2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01')),
+    PARTITION p_2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
+    PARTITION p_future VALUES LESS THAN MAXVALUE);
+</code>
+or
+<code>
+    PARTITION BY RANGE (YEAR(timestamp)) (
+    PARTITION p_pre2020 VALUES LESS THAN (2020),
+    PARTITION p_2020 VALUES LESS THAN (2021),
+    PARTITION p_2021 VALUES LESS THAN (2022),
+    PARTITION p_2022 VALUES LESS THAN (2023),
+    PARTITION p_2023 VALUES LESS THAN (2024),
+    PARTITION p_future VALUES LESS THAN MAXVALUE);
+</code>
+can also be partitioned by list:
+<code>
+    PARTITION BY LIST (customer_id) (
+    PARTITION p_cust_1 VALUES IN (1, 2, 3),
+    PARTITION p_cust_2 VALUES IN (4, 5, 6),
+    PARTITION p_cust_3 VALUES IN (7, 8, 9),
+    PARTITION p_others VALUES IN (DEFAULT));
+</code>
+or by name initial:
+<code>
+    PARTITION BY RANGE COLUMNS(customer_name) (
+    PARTITION p_a_f VALUES LESS THAN ('G'),
+    PARTITION p_g_m VALUES LESS THAN ('N'),
+    PARTITION p_n_s VALUES LESS THAN ('T'),
+    PARTITION p_t_z VALUES LESS THAN (MAXVALUE));
+</code>
+File sizes of individual partition are not updated until they are optimized:
+<code>
+OPTIMIZE TABLE newtable;
 </code>