8.4.2.2 Optimizing for Character and String Types

For character and string columns, follow these guidelines:

Use binary collation order for fast comparison and sort
operations, when you do not need language-specific
collation features. You can use the
BINARY operator
to use binary collation within a particular query.

When comparing values from different columns, declare
those columns with the same character set and collation
wherever possible, to avoid string conversions while
running the query.

For column values less than 8KB in size, use binary
VARCHAR instead of
BLOB. The GROUP BY
and ORDER BY clauses can generate
temporary tables, and these temporary tables can use the
MEMORY storage engine if the original
table does not contain any BLOB
columns.

If a table contains string columns such as name and
address, but many queries do not retrieve those columns,
consider splitting the string columns into a separate
table and using join queries with a foreign key when
necessary. When MySQL retrieves any value from a row, it
reads a data block containing all the columns of that row
(and possibly other adjacent rows). Keeping each row
small, with only the most frequently used columns, allows
more rows to fit in each data block. Such compact tables
reduce disk I/O and memory usage for common queries.

When you use a randomly generated value as a primary key
in an InnoDB table, prefix it with an
ascending value such as the current date and time if
possible. When consecutive primary values are physically
stored near each other, InnoDB can
insert and retrieve them faster.