mysql--Index

• Index overview

1) All MySQL column types can be indexed and using indexes on related columns is the best way to improve the performance of select operations.

2) Depending on the storage engine, you can define the maximum number of indexes and the maximum index length for each table, and each storage engine supports at least 16 indexes per table with a total index length of at least 256 bytes. Most storage engines have a higher limit.

3) The tables of the MyISAM and InnoDB storage engines are created by default as Btree indexes.

4) MySQL does not currently support function indexing, but it supports prefix indexing, which is to create indexes on the first n characters of an indexed field. The length of the prefix index is related to the storage engine, and for MyISAM storage engine tables, the index prefix length can reach 1000 byte word lengths, whereas for InnoDB storage engine tables, the index prefix length is most often 767 bytes. Note that the prefix limit should be measured in bytes, whereas the prefix length in the Create TABLE statement is interpreted as the number of characters.

5) MySQL also supports the full-text (fulltext) index, which can be used for full-text search. However, only the MyISAM storage engine in the current version (5.0) supports Fulltext indexes and is limited to Varchar,char and text columns. Indexes are always made for an entire column, and local (prefix) indexes are not supported.

6) You can also create indexes for spatial column types, but only the MyISAM storage engine supports spatial type indexes, and the indexed fields must be non-empty.

7) By default, the memory storage engine uses a hash index, but it also supports the Btree index.

8) The index can be created at the same time as the table is created, or you can add new indexes at any time. The syntax for creating a new index is:

CREATE [unique| Fulltext| SPATIAL] INDEX index_name

[USING Index_type]

On Tbl_name (Index_col_name, ...)

mysql--Index