Partitions and UPDATE

Question

I'm diving deeper and deeper into MySQL Features, and the next one I'm trying out is table partitions

There's basically only one question about them, where I couldn't find a clear answer yet:

If you UPDATE a row, will the row be moved to another partition automatically, if the partition conditions of another partition is met? (if for example, the partitions are split up by region, and the region changes from region A to region B)

And if that doesn't happen automatically, what do I need to do in order to move the row from partition A to partition B? (and will there be a performance hit by doing so?)

What I would like to do, is to move 'deleted' (a flag) informations into a separate partition of the table, since those will rarely be called. Would that usually be a good idea or would it be better to just leave everything in the same (probably someday huge - multiple million rows) table?

Answer 1

It must move them on update. If it didn't it wouldn't work well. MySQL would have to basically scan all partitions on every query as it couldn't know where records where stored.

I also did some tests (on MySQL 5.6 as that's the first version where it's possible to specify what partions to query)

CREATE TABLE test (
  id int
) 
PARTITION BY RANGE (id) (
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN MAXVALUE);

INSERT INTO test VALUES (1); -- now on partition p1

SELECT * FROM test PARTITION(p1);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SELECT * FROM test PARTITION(p2);
Empty set (0.00 sec)

UPDATE test SET id = 1001; -- now on partition p2

SELECT * FROM test PARTITION (p1);
Empty set (0.00 sec)

SELECT * FROM test PARTITION (p2);
+------+
| id   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

It's clear that it immediatly after the update know that the row is now on partition p2.