(mysql.info.gz) Data size

Info Catalog

(mysql.info.gz) Design

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) 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 give 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 a lot of different table types and row formats.  For each
 table, you can decide which storage/index method to use.  Choosing the
 right table format for your application may give you a big performance
 gain.   Storage engines.
 
 You can get better performance on a table and minimize storage space
 using the techniques listed here:
 
    * Use the most efficient (smallest) data types possible. MySQL has
      many specialized types that save disk space and memory.
 
    * Use the smaller integer types if possible to get smaller tables.
      For example, `MEDIUMINT' is often better than `INT'.
 
    * 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 don't have any variable-length columns
      (`VARCHAR', `TEXT', or `BLOB' columns), a fixed-size record format
      is used. This is faster but unfortunately may waste some space.
       `MyISAM' table formats MyISAM table formats.  You can hint
      that you want to have fixed length rows even if you have `VARCHAR'
      columns with the `CREATE' option `ROW_FORMAT=fixed'.
 
    * Starting with MySQL/InnoDB 5.0.3, `InnoDB' tables use a more
      compact storage format.  In earlier versions of MySQL, InnoDB
      records contain some redundant information, such as the number of
      columns and the lengths of each column, even for fixed-size
      columns.  By default, tables will be created in the compact format
      (`ROW_FORMAT=COMPACT').  If you wish to downgrade to older
      versions of MySQL/InnoDB, you can request the old format with
      `ROW_FORMAT=REDUNDANT'.
 
    * 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 things fast. If you mostly
      access a table by searching on a combination of columns, make an
      index on them. The first index part should be the most used
      column. If you are _always_ using many columns, you should use the
      column with more duplicates first to get better compression of the
      index.
 
    * If it's very likely that a column has a unique prefix on the first
      number of characters, it's better to index only this prefix. MySQL
      supports an index on the leftmost part of a character column.
      Shorter indexes are faster not only because they take less disk
      space, but also because they will give you more hits in the index
      cache and thus fewer disk seeks.   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.gz) Design

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Indexes