Oracle 5.0, Reference Manual

Page: 735 / 3232

Using
PROCEDURE ANALYSE
715
format (
ROW_FORMAT=COMPACT
). If you wish to downgrade to older versions of MySQL, you can
request the old format with
ROW_FORMAT=REDUNDANT
.
The presence of the compact row format decreases row storage space by about 20% at the cost of
increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit
rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might
be slower.
The compact
InnoDB
format also changes how
CHAR
columns containing UTF-8 data are
stored. With
ROW_FORMAT=REDUNDANT
, a UTF-8
CHAR(N)
occupies 3 ×
N
bytes, given that the
maximum length of a UTF-8 encoded character is three bytes. Many languages can be written
primarily using single-byte UTF-8 characters, so a fixed storage length often wastes space. With
ROW_FORMAT=COMPACT
format,
InnoDB
allocates a variable amount of storage in the range from
N
to 3 ×
N
bytes for these columns by stripping trailing spaces if necessary. The minimum storage
length is kept as
N
bytes to facilitate in-place updates in typical cases.
• 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 13.1.8, “
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 8.9.2, “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.
8.8.2. Using
PROCEDURE ANALYSE
ANALYSE([max_elements[,max_memory]])
ANALYSE()
examines the result from a query and returns an analysis of the results that suggests
optimal data types for each column that may help reduce table sizes. To obtain this analysis, append
PROCEDURE ANALYSE
to the end of a
SELECT
statement:
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([ max_elements ,[ max_memory ]])
For example:
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
The results show some statistics for the values returned by the query, and propose an optimal data
type for the columns. This can be helpful for checking your existing tables, or after importing new data.
You may need to try different settings for the arguments so that
PROCEDURE ANALYSE()
does not
suggest the
ENUM
data type when it is not appropriate.
The arguments are optional and are used as follows:
•
max_elements
(default 256) is the maximum number of distinct values that
ANALYSE()
notices per
column. This is used by
ANALYSE()
to check whether the optimal data type should be of type
ENUM
;
if there are more than
max_elements
distinct values, then
ENUM
is not a suggested type.
•
max_memory
(default 8192) is the maximum amount of memory that
ANALYSE()
should allocate per
column while trying to find all distinct values.