Mysql optimization: Schema Optimization

Very first step to optimization: Give a thought to your schema design


Avoid NULL if possible:

• 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.
• 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.

Whole Number:

• Your choice determines how MySQL stores the data, in memory and on disk. However, integer computations generally use 64-bit BIGINT integers, even on 32-bit architectures. (The exceptions are some aggregate functions, which use DECIMAL or DOUBLE to perform computations.)

Floating point number:

• Floating-point types typically use less space than DECIMAL to store the same range of values. A FLOAT column uses four bytes of storage. DOUBLE consumes eight bytes and has greater precision and a larger range of values. As with integers, you’re choosing only the storage type; MySQL uses DOUBLE for its internal calculations on floating- point types.

String type:

• Use varchar instead of char.
• Don't be generous while giving the length of varchar column. Keep some characters extra. Also don't give it extremely high. When you know 50 characters are fine, there is no need to give 255 characters for that column. Also max length of columns has some role in indexing (Biggest player in query optimization.) So keep this in mind.
  

To be continued .....