More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
MySQL - 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.
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.
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.
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.
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.
(Continued on next question...)
