7.3.9 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.
A table may have up to 16 indexes. The maximum index length is 256 bytes, although this may be changed when compiling MySQL.
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 thing.
Comments
