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).
Very interesting, the more I learn about InnoDB, the more I'm impressed. I guess I haven't encountered a column value that's bigger than 8KiB in an InnoDB database yet.
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.
Everything I've read says you're absolutely right about the performance boost lol. It very well may be the next piece I check out, thank you.