(mysql.info) data-size

Info Catalog

(mysql.info) design

(mysql.info) optimizing-database-structure

(mysql.info) indexes

 
 7.4.2 Make Your Data as Small as Possible
 -----------------------------------------
 
 One of the most basic optimizations is to design your tables to take as
 little space on the disk as possible. This can result in huge
 improvements because disk reads are faster, and smaller tables normally
 require less main memory while their contents are being actively
 processed during query execution. Indexing also is a lesser resource
 burden if done on smaller columns.
 
 MySQL supports many different storage engines (table types) and row
 formats. For each table, you can decide which storage and indexing
 method to use. Choosing the proper table format for your application
 may give you a big performance gain. See  storage-engines.
 
 You can get better performance for a table and minimize storage space
 by using the techniques listed here:
 
    * Use the most efficient (smallest) data types possible. MySQL has
      many specialized types that save disk space and memory.  For
      example, use the smaller integer types if possible to get smaller
      tables. `MEDIUMINT' is often a better choice than `INT' because a
      `MEDIUMINT' column uses 25% less space.
 
    * Declare columns to be `NOT NULL' if possible. It makes everything
      faster and you save one bit per column. If you really need `NULL'
      in your application, you should definitely use it. Just avoid
      having it on all columns by default.
 
    * For `MyISAM' tables, if you do not have any variable-length
      columns (`VARCHAR', `TEXT', or `BLOB' columns), a fixed-size row
      format is used. This is faster but unfortunately may waste some
      space. See  myisam-table-formats. You can hint that you
      want to have fixed length rows even if you have `VARCHAR' columns
      with the `CREATE TABLE' option `ROW_FORMAT=FIXED'.
 
    * Starting with MySQL 5.0.3, `InnoDB' tables use a more compact
      storage format. In earlier versions of MySQL, `InnoDB' rows
      contain some redundant information, such as the number of columns
      and the length of each column, even for fixed-size columns. By
      default, tables are created in the compact format
      (`ROW_FORMAT=COMPACT'). If you wish to downgrade to older versions
      of MySQL, you can request the old format with
      `ROW_FORMAT=REDUNDANT'.
 
      The compact `InnoDB' format also changes how `CHAR' columns
      containing UTF-8 data are stored. With `ROW_FORMAT=REDUNDANT', a
      UTF-8 `CHAR(N)' occupies 3 × N bytes, given that the maximum
      length of a UTF-8 encoded character is three bytes. Many languages
      can be written primarily using single-byte UTF-8 characters, so a
      fixed storage length often wastes space. With `ROW_FORMAT=COMPACT'
      format, `InnoDB' allocates a variable amount of storage in the
      range from N to 3 × N bytes for these columns by stripping
      trailing spaces if necessary. The minimum storage length is kept
      as N bytes to facilitate in-place updates in typical cases.
 
    * The primary index of a table should be as short as possible.  This
      makes identification of each row easy and efficient.
 
    * Create only the indexes that you really need. Indexes are good for
      retrieval but bad when you need to store data quickly. If you
      access a table mostly by searching on a combination of columns,
      create an index on them. The first part of the index should be the
      column most used. If you _always_ use many columns when selecting
      from the table, you should use the column with more duplicates
      first to obtain better compression of the index.
 
    * If it is very likely that a string column has a unique prefix on
      the first number of characters, it's better to index only this
      prefix, using MySQL's support for creating an index on the
      leftmost part of the column (see  create-index). Shorter
      indexes are faster, not only because they require less disk space,
      but because they give also you more hits in the index cache, and
      thus fewer disk seeks. See  server-parameters.
 
    * In some circumstances, it can be beneficial to split into two a
      table that is scanned very often. This is especially true if it is
      a dynamic-format table and it is possible to use a smaller static
      format table that can be used to find the relevant rows when
      scanning the table.
 

Info Catalog

(mysql.info) design

(mysql.info) optimizing-database-structure

(mysql.info) indexes