GNU Info

(mysql.info)Data size

---

Next: MySQL indexes Prev: Design Up: Optimizing Database Structure

---

Enter node , (file) or (file)node

Get Your Data as Small as Possible
----------------------------------

One of the most basic optimization is to get your data (and indexes) to
take as little space on the disk (and in memory) as possible. This can
give huge improvements because disk reads are faster and normally less
main memory will be used. Indexing also takes less resources if done on
smaller columns.

MySQL supports a lot of different table types and row formats.
Choosing the right table format may give you a big performance gain.
Note: Table types.

You can get better performance on a table and minimize storage space
using the techniques listed below:

   * Use the most efficient (smallest) 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. Note that if you really
     need `NULL' in your application you should definitely use it. Just
     avoid having it on all columns by default.

   * 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.  Note: MyISAM table
     formats.

   * The primary index of a table should be as short as possible. This
     makes identification of one row easy and efficient.

   * For each table, you have to decide which storage/index method to
     use. Note: Table types.

   * Only create 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 only index this prefix. MySQL
     supports an index on a 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. Note: 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.