5.4.4 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 storage engine. See section 7 MySQL Table Types. You can with all storage engines 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 here 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 CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column and partial indexing is not supported. See section 6.8 MySQL Full-text Search for details.

User Comments

Add your own comment.