CREATE [UNIQUE] INDEX index_name ON table_name ( column_name[(length]),... )
This function doesn't do anything in MySQL version before version 3.22.
This is mapped to a ALTER TABLE call to create indexes.
See section ALTER TABLE syntax
Normally one creates all INDEX at the same time with CREATE TABLE
See section CREATE TABLE syntax.
(col1, col2) creates a multiple index over the two columns.
The index can be seen as a concatenation of the given columns. If you in
CREATE TABLE use INDEX(col1), INDEX(col2) instead
of INDEX(col1,col2) you get two separate indexes instead of one
multiple index.
SELECT * FROM table WHERE col1=# AND col2=#
In a case of an index on (col1,col2) the right row(s) can be fetched
directly. In a case of (col1), (col2) the optimizer decides
which index will find fewer rows and this index will be used to fetch the rows.
If the table has an index (col1,col2,col3...) the prefix
of this can be used by the optimiser to find the rows. This means
that the above gives you search capabilities on: (col1)
and (col1,col2) and (col1,col2,col3)...
MySQL can't use a portion of an index to locate rows through an index.
With the definition (col1,col2,col3):
SELECT * FROM table WHERE col1=# SELECT * FROM table WHERE col2=# SELECT * FROM table WHERE col2=# and col3=#
only the first query will use indexes.
MySQL will also use indexes if the LIKE argument is a
constant string that doesn't start with a wild character:
The following will use indexes:
SELECT * from table WHERE key_column like "Patrick%"; SELECT * from table WHERE key_column like "Pat%_ck%";
In the above cases only rows with Patrick <= key_column < Patricl and
Pat <= key_column < Pau will be considered.
The following selects will not use indexes:
SELECT * from table WHERE key_column like "%Patrick%"; SELECT * from table WHERE key_column like other_column;
With column_name(length) syntax one can specify an index which is only
a part of a string column. This can make the index file much smaller.
CREATE INDEX part_of_name ON customer (name(10))
As it's quite normal that most names differs in the first 10 characters, the above definition should not slow down searches on names, but it could save a lot of disk and even speed up inserts!
Go to the first, previous, next, last section, table of contents.