Bookmark and Share
MySQL table design tips
(Publish Date: 2009-8-18 11:53am, Total Visits: 1806, Today: 1, This Week: 2, This Month: 9)

Here is the pattern for mysql faster performance:

Smaller Data Types => Narrower Index Records => More records Per Block => Fewer Reads

1. Don't use "text" data type if possible

    The "text" data type will force MySQL to create temporary tables on disk,
    rather than handling them in memory. This will cause extra disk I/O.
    (suggest to put the MySQL temp directory on the tmp file system if you really need "text" columns).

    Instead of using "text" data type, please try to use varchar(), the longest varchar in MySQL 5.0
    can be 64k, which is around 32 pages of the letter size paper. This is enough for general articles.

(please refer http://dev.mysql.com/doc/refman/5.0/en/blob.html )

2. Use columns that are no longer than necessary.

    For example, don't use BIGINT if MEDIUMINT will do,
    or CHAR(255) if strings are never more than 100 characters long.

3. Define columns as NOT NULL if possible.

    It’s harder for MySQL to optimize queries that refer to nullable columns,
    because they make indexes, index statistics, and value comparisons more complicated.
    A nullable column uses more storage space and requires special processing
    inside MySQL. When a nullable column is indexed, it requires an extra
    byte per entry and can even cause a fixed-size index (such as an index on a   single integer column) to be converted to a variable-sized one in MyISAM.

    Even when you do need to store a “no value” fact in a table, you might not need
    to use NULL. Consider using zero, a special value, or an empty string instead.

   The performance improvement from changing NULL columns to NOT NULL is usually
   small, so don’t make finding and changing them on an existing schema a priority
   unless you know they are causing problems. However, if you’re planning to
   index columns, avoid making them nullable if possible.

4. For MyISAM tables

    1) Use fixed-length columns (CHAR) for best speed

       CREATE TABLE t (c CHAR(50)) ROW_FORMAT = FIXED;

    2) Use variable-length columns (VARCHAR, TEXT, BLOB) for best use of disk space.

       ALTER TABLE t ROW_FORMAT = DYNAMIC;

    3) splitting the table into two tables. Use a fixed-format table to hold the
       fixed-length columns and a dynamic-format table to hold the variable-length columns.

5. To analyze column contents, use PROCEDURE ANALYSE().

    mysql> SELECT * FROM image_rights PROCEDURE ANALYSE(10,256)\G;

    *************************** 1. row ***************************
                 Field_name: hearst.image_rights.image_rights_id
                  Min_value: 1314
                  Max_value: 1139520
                 Min_length: 4
                 Max_length: 7
           Empties_or_zeros: 0
                      Nulls: 0
    Avg_value_or_avg_length: 648799.8385
                        Std: 709380.8480
          Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL
    *************************** 2. row ***************************
                 Field_name: hearst.image_rights.syndicate_id
                  Min_value: 2
                  Max_value: 47
                 Min_length: 1
                 Max_length: 2
           Empties_or_zeros: 0
                      Nulls: 0
    Avg_value_or_avg_length: 24.8820
                        Std: 12.0320
          Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL
    *************************** 3. row ***************************
                 Field_name: hearst.image_rights.image_id
                  Min_value: 2291
                  Max_value: 249198
                 Min_length: 4
                 Max_length: 6
           Empties_or_zeros: 0
                      Nulls: 0
    Avg_value_or_avg_length: 185170.0679
                        Std: 191070.4845
          Optimal_fieldtype: MEDIUMINT(6) UNSIGNED NOT NULL
    *************************** 4. row ***************************
                 Field_name: hearst.image_rights.allow_use
                  Min_value: 1
                  Max_value: 1
                 Min_length: 1
                 Max_length: 1
           Empties_or_zeros: 4927
                      Nulls: 0
    Avg_value_or_avg_length: 0.9904
                        Std: 0.0976
          Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL

6. Use proper indexing for your tables

7. Normalize your tables and Use summary tables.

8. Consider horizontally splitting many-columned tables

9. Consider vertically splitting many-rowed tables using partitioning( version 5.1) or Merge tables

10. Always remember...fewer reads = faster result