Databases Reference
In-Depth Information
designed for low overhead and simplicity. For example, there's no need for the filesys-
tem to make any provisions for recovery. That makes it faster.
The server setting that controls where temporary tables are placed is
tmpdir
. Monitor
how full the filesystem gets to ensure you have enough space for temporary tables. If
necessary, you can even specify several temporary table locations, which MySQL will
use in a round-robin fashion.
If your
BLOB
columns are very large and you use InnoDB, you might also want to
increase InnoDB's log buffer size. We wrote more about this earlier in this chapter.
For long variable-length columns (e.g.,
BLOB
,
TEXT
, and long character columns),
InnoDB stores a 768-byte prefix in-page with the rest of the row.
16
If the column's value
is longer than this prefix length, InnoDB might allocate external storage space outside
the row to store the rest of the value. It allocates this space in whole 16 KB pages, just
like all other InnoDB pages, and each column gets its own page (columns do not share
external storage space). InnoDB allocates external storage space to a column a page at
a time until 32 pages are used; then it allocates 64 pages at a time.
Note that we said InnoDB
might
allocate external storage. If the total length of the row,
including the full value of the long column, is shorter than InnoDB's maximum row
length (a little less than 8 KB), InnoDB will not allocate external storage even if the long
column's value exceeds the prefix length.
Finally, when InnoDB updates a long column that is placed in external storage, it
doesn't update it in place. Instead, it writes the new value to a new location in external
storage and deletes the old value.
All of this has the following consequences:
• Long columns can waste a lot of space in InnoDB. For example, if you store a
column value that is one byte too long to fit in the row, it will use an entire page
to store the remaining byte, wasting most of the page. Likewise, if you have a value
that is slightly more than 32 pages long, it
might
actually use 96 pages on disk.
• External storage disables the adaptive hash index, which needs to compare the full
length of columns to verify that it has found the right data. (The hash helps InnoDB
find “guesses” very quickly, but it must check that its “guess” is correct.) Because
the adaptive hash index is completely in-memory and is built directly “on top of”
frequently accessed pages in the buffer pool, it doesn't work with external storage.
• Long values can make any query with a
WHERE
clause that doesn't use an index run
slowly. MySQL reads all columns before it applies the
WHERE
clause, so it might ask
InnoDB to read a lot of external storage, then check the
WHERE
clause and throw
away all the data it read. It's never a good idea to select columns you don't need,
16. This is long enough to create a 255-character index on a column, even if it's
utf8
, which might require
up to three bytes per character. This prefix is specific to the Antelope InnoDB file format; it doesn't apply
to the Barracuda format, which is available in MySQL 5.1 and newer (though not enabled by default).
