(mysql.info.gz) Indexes

Info Catalog

(mysql.info.gz) Data size

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Multiple-column indexes

 
 7.4.3 Column Indexes
 --------------------
 
 All MySQL column types can be indexed.  Use of indexes on the relevant
 columns is the best way to improve the performance of `SELECT'
 operations.
 
 The maximum number of indexes per table and the maximum index length is
 defined per storage engine.  Storage engines. All storage
 engines support at least 16 indexes per table and a total index length
 of at least 256 bytes.  Most storage engines have higher limits.
 
 With `COL_NAME(LENGTH)' syntax in an index specification, you can
 create an index that uses only the first LENGTH characters of a `CHAR'
 or `VARCHAR' column. Indexing only a prefix of column values like this
 can make the index file much smaller.
 
 The `MyISAM' and (as of MySQL 4.0.14) `InnoDB' storage engines also
 support indexing on `BLOB' and `TEXT' columns.  When indexing a `BLOB'
 or `TEXT' column, you _must_ specify a prefix length for the index. For
 example:
 
      CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
 
 Prefixes can be up to 255 bytes long (or 1000 bytes for `MyISAM' and
 `InnoDB' tables as of MySQL 4.1.2).  Note that prefix limits are
 measured in bytes, whereas the prefix length in `CREATE TABLE'
 statements is interpreted as number of characters.  Take this into
 account when specifying a prefix length for a column that uses a
 multi-byte character set.
 
 As of MySQL 3.23.23, you can also create `FULLTEXT' indexes.  They are
 used for full-text searches. Only the `MyISAM' table type supports
 `FULLTEXT' indexes and only for `CHAR', `VARCHAR', and `TEXT' columns.
 Indexing always happens over the entire column and partial (prefix)
 indexing is not supported. See  Fulltext Search for details.
 
 As of MySQL 4.1.0, you can create indexes on spatial column types.
 Currently, spatial types are supported only by the `MyISAM' storage
 engine. Spatial indexes use R-trees.
 
 The `MEMORY' (`HEAP') storage engine uses hash indexes by default.  It
 also supports B-tree indexes as of MySQL 4.1.0.
 

Info Catalog

(mysql.info.gz) Data size

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Multiple-column indexes