Not guaranteed. The DYNAMIC (default) and COMPRESSED row formats [0] can shift larger values of variable-length columns in off-page records. In the example given, this wouldn’t occur, but it isn’t a guarantee per se. This is roughly equivalent to Postgres’ TOAST, though Postgres’ default threshold for TOAST is 1/4 of a page (2 KiB with its default 8 KiB page size), whereas InnoDB is 1/2 of a page (8 KiB with its default 16 KiB page size).
I like the view into the personal journey learning new things and sharing it.
Suggested next steps would be trying out DuckDB instead. My guess is it would smoke spark etc at this scale. Always good to learn different corners of the data landscape.
Rows store all columns together
Not guaranteed. The DYNAMIC (default) and COMPRESSED row formats [0] can shift larger values of variable-length columns in off-page records. In the example given, this wouldn’t occur, but it isn’t a guarantee per se. This is roughly equivalent to Postgres’ TOAST, though Postgres’ default threshold for TOAST is 1/4 of a page (2 KiB with its default 8 KiB page size), whereas InnoDB is 1/2 of a page (8 KiB with its default 16 KiB page size).
0: https://dev.mysql.com/doc/refman/9.7/en/innodb-row-format.ht...
Suggested next steps would be trying out DuckDB instead. My guess is it would smoke spark etc at this scale. Always good to learn different corners of the data landscape.