Mysql – How does DYNAMIC Row format handle NULL storage MYSQL 5.7

According to MySQL 5.7 documentation (current as of now):

8.8.2 EXPLAIN Output Format

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

Emphasis mine. That is, I guess that MySQL isn't intelligent enough to remember that "it already looked some key".

MariaDB uses several Block-Based Join Algorithms in current versions:

• Block Nested Loop (BNL) join
• Block Nested Loop Hash (BNLH) join
• Block Index join known as Batch Key Access (BKA) join
• Block Index Hash join known as Batch Key Access Hash (BKAH) join

The "Batch Key Access" methods seems to take some advantage ... the key part of the docs is:

Yet BKA join allows to optimize the order in which the records are fetched.

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- Or COMPRESSED

That is what was needed in 5.6, I think. Since Barracuda and file_per_table are defaults in 5.7, most of this goes away. However, the upgrade may have left the table Antelope and/or not file_per_table.

So, I recommend throwing those 3 commands and see if it "fixes" your problem for good. (Yeah, keep some kind of backup somewhere first.)

You mentioned utf8; perhaps you want utf8mb4, too?

ALTER TABLE tbl CONVERT TO utf8mb4;

(I don't now if replication will be a problem. Presumably after the above changes it won't be a problem on a 5.7 Slave.)

Edit

But... Being the default (for 5.7) does not mean that you got those values for an existing file after an upgrade.

Look in information_schema.INNODB_SYS_TABLES to see what the file_format and row_format are. The column space indicates file_per_table if the value is > 0.

A drastic way to deal with the problem: Change the InnoDB page size. The default is 16KB, but 32KB is possible. If I am not mistaken, all tables on the system have to be converted to the new page size, so it is a non-trivial undertaking, and may have other ramifications.

The handling of VARCHAR in DYNAMIC/COMPRESSED

When is the string stored in the main part of the record, versus stored in some other block?

• Small size (string is < 40 bytes): The entire string is stored with the record. 2+N bytes.
• Medium size: Either 'small' or 'large', depending on "record not too big".
• Large size: 2+20 bytes in record (length+pointer); entire text stored separately.

"record too big": When the row is too long, the longest columns are stored in off-page storage. That is, some times a column will be entirely off-page, sometimes on-page, depending on the makeup of the entire row.

VARCHAR and TEXT and BLOB are handled the same for DYNAMIC or COMPRESSED.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page ("all or none").

COMPRESSED = DYNAMIC + compression. KEY_BLOCK_SIZE controls how much column data is stored in the clustered index, and how much is placed on overflow pages.

5.7 reference and older, more detailed, discussion; and other pages.