Table scans read only the InnoDB data page unless off-page column values are referenced. What's in one of those pages?

"If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page." -- http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

The default InnoDB data page size is 16k bytes (not characters), so if a column value size > 8k, InnoDB stores it off-page. If total row size > 8k, smaller values may be off-page.

If queries on a table often pay this price, the extra execution time required by a join to a linked table holding such coluns may be small, and execution time may this improve for those queries that do not require off-page access.

Last updated 16 Aug 2019