Mysql – In what cases are BLOB and TEXT stored in-line on InnoDB

blobdatabase-internalsinnodbMySQLstorage

I've googled this and can't find it, you can see Rick James mention it here.

… BLOB and TEXT are not always stored separately

The answer on What is the difference between MySQL VARCHAR and TEXT data types? says,

InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

That's not the only massively upvote answer with this stance. From StackOverflow you can find an answer with 754 upvotes that says it quite clearly,

TEXT and BLOB is stored off the table with the table just having a pointer to the location of the actual storage.

VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a realworld scenario with your data.

The comment here links to a post on a mailing list that says

MyISAM puts TEXT and BLOB 'inline'. If you are searching a table (range scan / table scan), you are 'stepping over those cow paddies' — costly for disk I/O. That is, the existence of the inline blob hurts performance in this case.

InnoDB puts only 767 bytes of a TEXT or BLOB inline, the rest goes into some other block. This is a compromise that sometimes helps, sometimes hurts performance.

I've seen other posts that talk about storing text and blobs in-line if they're under some limit? What is the limit? When, if ever, does InnoDB store blobs and texts in line?

Best Answer

For InnoDB the rule goes like this:

  • A record can not be fragmented.
  • At least two records must fit in a page.
  • If a record size is less than ~7k, all field values are stored in-page.
  • If the record size is more than ~7k the first 768 (COMPACT format) or 20 bytes (DYNAMIC format) are stored in-page. The remaining part is stored off-page.

How to verify that.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


mysql> insert into t1(name) select repeat('a', 10);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1(name) select repeat('a', 8000);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Then parse the t1.ibd with stream_parser.

stream_parser -f t1.ibd

Check content of the PRIMARY index page:

# hexdump -C pages-t1.ibd/FIL_PAGE_INDEX/0000000000000046.page
00000000  97 e5 5e 36 00 00 00 03  ff ff ff ff ff ff ff ff  |..^6............|
00000010  00 00 00 00 00 27 b9 44  45 bf 00 00 00 00 00 00  |.....'.DE.......|
00000020  00 00 00 00 00 18 00 02  1f f3 80 04 00 00 00 00  |................|
00000030  00 a2 00 02 00 01 00 02  00 00 00 00 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 2e 00 00 00 18 00 00  |................|
00000050  00 02 00 f2 00 00 00 18  00 00 00 02 00 32 01 00  |.............2..|
00000060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
00000070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 23 80  |supremum......#.|
00000080  00 00 01 00 00 00 00 05  10 ae 00 00 01 22 01 10  |............."..|
00000090  61 61 61 61 61 61 61 61  61 61 40 9f 00 00 00 18  |aaaaaaaaaa@.....| <- this is the first record
000000a0  ff ce 80 00 00 02 00 00  00 00 05 11 af 00 00 01  |................|
000000b0  23 01 10 61 61 61 61 61  61 61 61 61 61 61 61 61  |#..aaaaaaaaaaaaa| <- this is the beginning of the second record
000000c0  61 61 61 61 61 61 61 61  61 61 61 61 61 61 61 61  |aaaaaaaaaaaaaaaa|
* <- The star means identical strings are omitted, -v will show full output.
00001ff0  61 61 61 00 00 00 00 00  00 00 00 00 00 00 00 00  |aaa.............|
00002000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00003ff0  00 00 00 00 00 70 00 63  97 e5 5e 36 00 27 b9 44  |.....p.c..^6.'.D|
00004000  97 e5 5e 36 00 00 00 03  ff ff ff ff ff ff ff ff  |..^6............|
00004010  00 00 00 00 00 27 b9 44  45 bf 00 00 00 00 00 00  |.....'.DE.......|
00004020  00 00 00 00 00 18 00 02  1f f3 80 04 00 00 00 00  |................|
00004030  00 a2 00 02 00 01 00 02  00 00 00 00 00 00 00 00  |................|
00004040  00 00 00 00 00 00 00 00  00 2e 00 00 00 18 00 00  |................|
00004050  00 02 00 f2 00 00 00 18  00 00 00 02 00 32 01 00  |.............2..|
00004060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
00004070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 23 80  |supremum......#.|
00004080  00 00 01 00 00 00 00 05  10 ae 00 00 01 22 01 10  |............."..|
00004090  61 61 61 61 61 61 61 61  61 61 40 9f 00 00 00 18  |aaaaaaaaaa@.....|
000040a0  ff ce 80 00 00 02 00 00  00 00 05 11 af 00 00 01  |................|
000040b0  23 01 10 61 61 61 61 61  61 61 61 61 61 61 61 61  |#..aaaaaaaaaaaaa|
000040c0  61 61 61 61 61 61 61 61  61 61 61 61 61 61 61 61  |aaaaaaaaaaaaaaaa|
*
00005ff0  61 61 61 00 00 00 00 00  00 00 00 00 00 00 00 00  |aaa.............|
00006000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00007ff0  00 00 00 00 00 70 00 63  97 e5 5e 36 00 27 b9 44  |.....p.c..^6.'.D|
00008000

This is for COMPACT format. For DYNAMIC (except COMPRESSED) the procedure is same.

Related Question