Optimizing MySQL Data Types you can relax the normalization rules, duplicating information or creating summary tables to gain more speed. •For unique IDs or other values that can be represented as either strings or numbers, prefer numeric columns to string columns. Since large numeric values can be stored in fewer bytes than the corresponding strings, it is faster and takes less memory to transfer and compare them. •If you are using numeric data, it is faster in many cases to access information from a database (using a live connection) than to access a text file. Information in the database is likely to be stored in a more compact format than in the text file, so accessing it involves fewer disk accesses. You also save code in your application because you can avoid parsing the text file to find line and column boundaries. •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. •See Section 8.4.2.1, “Optimizing for Numeric Data”  for reasons why a numeric column is usually preferable to an equivalent string column. •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 1735 8.4.2.3 Optimizing for BLOB Types 8.4.2.2 Optimizing for Character and String Types 8.4.2 Optimizing MySQL Data Types 8.4.2.1 Optimizing for Numeric Data For character and string columns, follow these guidelines: