CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )
  • The CREATE INDEX query takes no action on versions of MySQL older than version 3.22. In version 3.22 and later, CREATE INDEX is equivalent to an ALTER TABLE query to create indexes. ALTER TABLE syntax.Normally, all indexes are created at the same time as the table itself with CREATE TABLE. Syntax of CREATE TABLE.CREATE INDEX allows you to add indexes to an existing table.
  • A list of columns of the form (col1, col2, …) creates a multi-column index. Index values ​​are created by concatenating the value of x given columns.
  • For CHAR and VARCHAR columns, indexes can be created on only part of the column, with the syntax col_name (length). (For BLOB and TEXT columns the index length is required.) The following query creates an index using the first 10 characters of the name column:
mysql> CREATE INDEX part_of_name ON customer (name(10));
  • Since most names usually have differences in the first 10 characters, the index should not be slower than an index created from the entire name column. Thus, by using only part of the column for indexes, one can reduce the size of the index file, which can save a lot of disk space, and can also speed up INSERT operations! It is important to know that you can index a column that can be NULL or a BLOB / TEXT column only if you are using version 3.23.2 or higher of MySQL and using the MyISAM type.
  • for more information about using indexes in MySQL, see How MySQL Uses Indexes .
  • FULLTEXT indexes can only index VARCHAR or TEXT columns, and only MyISAM tables. FULLTEXT indexes are available in MySQL version 3.23.23 and later. Full-text search with MySQL .