One of the most basic optimizations is to design your tables to take as little space on the disk as possible. This can result in huge improvements because disk reads are faster, and smaller tables normally require less main memory while their contents are being actively processed during query execution. Indexing also is a lesser resource burden if done on smaller columns.
MySQL supports many different storage engines (table types) and row formats. For each table, you can decide which storage and indexing method to use. Choosing the proper table format for your application may give you a big performance gain. See Chapter 13, Storage Engines.
You can get better performance for a table and minimize storage space by using the techniques listed here:
Use the most efficient (smallest) data types possible. MySQL
has many specialized types that save disk space and memory.
For example, use the smaller integer types if possible to
get smaller tables. MEDIUMINT
is often a better choice than
INT
because a
MEDIUMINT
column uses 25%
less space.
Declare columns to be NOT NULL
if
possible. It makes everything faster and you save one bit
per column. If you really need NULL
in
your application, you should definitely use it. Just avoid
having it on all columns by default.
For MyISAM
tables, if you do not have any
variable-length columns
(VARCHAR
,
TEXT
, or
BLOB
columns), a fixed-size
row format is used. This is faster but unfortunately may
waste some space. See
Section 13.1.3, “MyISAM
Table Storage Formats”. You can hint that
you want to have fixed length rows even if you have
VARCHAR
columns with the
CREATE TABLE
option
ROW_FORMAT=FIXED
.
The primary index of a table should be as short as possible. This makes identification of each row easy and efficient.
Create only the indexes that you really need. Indexes are good for retrieval but bad when you need to store data quickly. If you access a table mostly by searching on a combination of columns, create an index on them. The first part of the index should be the column most used. If you always use many columns when selecting from the table, the first column in the index should be the one with the most duplicates to obtain better compression of the index.
If it is very likely that a string column has a unique
prefix on the first number of characters, it is better to
index only this prefix, using MySQL's support for creating
an index on the leftmost part of the column (see
Section 12.1.4, “CREATE INDEX
Syntax”). Shorter indexes are faster,
not only because they require less disk space, but because
they also give you more hits in the index cache, and thus
fewer disk seeks. See Section 7.5.3, “Tuning 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.
User Comments
Add your own comment.