GNU Info

(mysql.info)Indexes

---

Next: Multiple-column indexes Prev: MySQL indexes Up: Optimizing Database Structure

---

Enter node , (file) or (file)node

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 keys and the maximum index length is defined per
table handler. Note: Table types. You can with all table handlers have
at least 16 keys and a total index length of at least 256 bytes.

For `CHAR' and `VARCHAR' columns, you can index a prefix of a column.
This is much faster and requires less disk space than indexing the
whole column.  The syntax to use in the `CREATE TABLE' statement to
index a column prefix looks like this:

     KEY index_name (col_name(length))

The example below creates an index for the first 10 characters of the
`name' column:

     mysql> CREATE TABLE test (
                name CHAR(200) NOT NULL,
                KEY index_name (name(10)));

For `BLOB' and `TEXT' columns, you must index a prefix of the column.
You cannot index the entire column.

In MySQL Version 3.23.23 or later, you can also create special
*FULLTEXT* indexes. They are used for full-text search. Only the
`MyISAM' table type supports `FULLTEXT' indexes. They can be created
only from `VARCHAR' and `TEXT' columns.  Indexing always happens over
the entire column and partial indexing is not supported. See Note:
Fulltext Search for details.