MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
When storing a large blob containing textual data,
consider compressing it first. Do not use this technique
when the entire table is compressed by
InnoDB
or MyISAM
.
For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed.
Since the performance requirements to retrieve and display a BLOB value might be very different from other data types, you could put the BLOB-specific table on a different storage device or even a separate database instance. For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a traditional hard drive than to an SSD device.
See Section 8.4.2.2, “Optimizing for Character and String Types” for reasons why a
binary VARCHAR
column is sometimes
preferable to an equivalent BLOB column.
Rather than testing for equality against a very long text
string, you can store a hash of the column value in a
separate column, index that column, and test the hashed
value in queries. (Use the MD5()
or
CRC32()
function to produce the hash
value.) Since hash functions can produce duplicate results
for different inputs, you still include a clause
AND
in
the query to guard against false matches; the performance
benefit comes from the smaller, easily scanned index for
the hashed values.
blob_column
=
long_string_value