Hi guys, today we have Horizontal partition, a nice feature that IMHO need a lock per partition instead a lock per table, and query cache invalidate per partition instead invalidate per table (maybe some work is being done at MDEV-252 at mysql server)
Now i'm thinking about Vertical partition (some column in one file, others columns in another files, etc...)
Instead of create table 1 and 2, with same primary key and different columns
Could we create a table X that have all columns and partition it vertically?
one point ... the lock MUST be done per partition
i'm thinking about partition1 = static values (with only insert)
partition2 = dynamic values (with many updates)
All partitions have the same number of ROWS with the same PRIMARY KEYS (it must have a UNIQUE INDEX, or a PRIMARY KEY to work)
when i execute
only table/partition X#p1 will be locked, in other words...
if i execute:
the first one (1) will not block the second (2), and the second (2) will not block the first (1), but:
the first one (3) will block all partitions from table X, and the second (4) will only be executed after (3) be completed