Oracle 5.0, Reference Manual

Page: 716 / 3232

MyISAM
Index Statistics Collection
696
... WHERE index_part1 =1 AND index_part2 =2 AND other_column =3
/* index = 1 OR index = 2 */
... WHERE index =1 OR A=10 AND index =2
/* optimized like " index_part1 ='hello'" */
... WHERE index_part1 ='hello' AND index_part3 =5
/* Can use index on index1 but not on index2 or index3 */
... WHERE index1 =1 AND index2 =2 OR index1 =3 AND index3 =3;
These
WHERE
clauses do not use indexes:
/* index_part1 is not used */
... WHERE index_part2 =1 AND index_part3 =2
/* Index is not used in both parts of the WHERE clause */
... WHERE index =1 OR A=10
/* No index spans all rows */
... WHERE index_part1 =1 OR index_part2 =10
Sometimes MySQL does not use an index, even if one is available. One circumstance under which
this occurs is when the optimizer estimates that using the index would require MySQL to access a
very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster
because it requires fewer seeks.) However, if such a query uses
LIMIT
to retrieve only some of the
rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in
the result.
Hash Index Characteristics
Hash indexes have somewhat different characteristics from those just discussed:
• They are used only for equality comparisons that use the
=
or
<=>
operators (but are very fast). They
are not used for comparison operators such as
<
that find a range of values.
• The optimizer cannot use a hash index to speed up
ORDER BY
operations. (This type of index cannot
be used to search for the next entry in order.)
• MySQL cannot determine approximately how many rows there are between two values (this is used
by the range optimizer to decide which index to use). This may affect some queries if you change a
MyISAM
table to a hash-indexed
MEMORY
table.
• Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key
can be used to find rows.)
8.5.4.
MyISAM
Index Statistics Collection
Storage engines collect statistics about tables for use by the optimizer. Table statistics are based
on value groups, where a value group is a set of rows with the same key prefix value. For optimizer
purposes, an important statistic is the average value group size.
MySQL uses the average value group size in the following ways:
• To estimate how may rows must be read for each
ref
[646] access
• To estimate how many row a partial join will produce; that is, the number of rows that an operation of
this form will produce:
(...) JOIN tbl_name ON tbl_name . key = expr
As the average value group size for an index increases, the index is less useful for those two purposes
because the average number of rows per lookup increases: For the index to be good for optimization
purposes, it is best that each index value target a small number of rows in the table. When a given
index value yields a large number of rows, the index is less useful and MySQL is less likely to use it.
The average value group size is related to table cardinality, which is the number of value groups. The
SHOW INDEX
statement displays a cardinality value based on
N
/
S
, where
N
is the number of rows in the