(mysql.info) indexes

Info Catalog

(mysql.info) data-size

(mysql.info) optimizing-database-structure

(mysql.info) multiple-column-indexes

 
 7.4.3 Column Indexes
 --------------------
 
 All MySQL data 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. See  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(N)' syntax in an index specification, you can create an
 index that uses only the first N characters of a string column.
 Indexing only a prefix of column values in this way can make the index
 file much smaller. When you index 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 1000 bytes long (767 bytes for `InnoDB' tables).
 Note that prefix limits are measured in bytes, whereas the prefix
 length in `CREATE TABLE' statements is interpreted as number of
 characters. _Be sure to take this into account when specifying a prefix
 length for a column that uses a multi-byte character set_.
 
 You can also create `FULLTEXT' indexes. These are used for full-text
 searches. Only the `MyISAM' storage engine supports `FULLTEXT' indexes
 and only for `CHAR', `VARCHAR', and `TEXT' columns. Indexing always
 takes place over the entire column and partial (column prefix) indexing
 is not supported. For details, see  fulltext-search.
 
 You can also create indexes on spatial data types. Currently, only
 `MyISAM' supports R-tree indexes on spatial types. As of MySQL 5.0.16,
 other storage engines use B-trees for indexing spatial types (except for
 `ARCHIVE' and `NDBCLUSTER', which do not support spatial type indexing).
 
 The `MEMORY' storage engine uses `HASH' indexes by default, but also
 supports `BTREE' indexes.
 

Info Catalog

(mysql.info) data-size

(mysql.info) optimizing-database-structure

(mysql.info) multiple-column-indexes