Mysql Optimization - schema optimization

1. Choose optimal data type
a. smaller is better - less space on disk, in memory, in CPU cache
b. simple is good (use integer instead of ip address, for example);
c. avoid null if possible - null requires special processing, makes index, comparisons more complicated.

2.Index strategies
a. isolate the column. mysql can't use index unless the columns are isolated in query
e.g., suppose id is primary key.
select * from test where id+1 = 5; should be changed to select * from test where id = 4

b. Prefix index
e.g., if u need to index long character columns, index the first few characters
alter table test add key (long_name(5));
downside is prefix index cannot be used for order by or group by

c. covering indexex
e.g. an index containing all the data needed for a query is covering index
-index entries are smaller and faster than row size
-indexes are sorted by their values

Normalization and Denormalization
A mixture of Normalized and Denormalized