mysql  create index statement

an index can be created in a table to find data more quickly and efficiently.

the create index statement is used to create indexes in tables.

mysql create index syntax

             create      [unique | fulltext | spatial]      index    index_name

                              using    [btree | hash | rtree]
                              on  table_name  (column_name)

unique means mysql will create a constraint that all values in the index must be unique.

fulltext index is supported only by myisam storage engine and only accepted on column that has data type is char, varchar, text.

spatial index supports spatial column and is available on myisam storage addition, the column value must not be null.

the index type define with keyword btree, hash, rtree which based on the storage engine of the table.

here are the storage engines of the table with the corresponding allowed index types:

storage engineindex types
ndbbtree, hash

mySQL create index example

the mysql statement below creates an index named "uindex" on the "firstname" column in the "users" table:

           create index      uindex   

                      on   users (firstname)

note :if you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

          create   index   uindex

                       on  users(firstname,salary)


if you want to removing indexes then you can use the MYSQL DROP Index statement.

you can also remove index by using the drop index statement.

the drop index statement is also mapped to alter table statement.

syntax :

           drop   index    index_name    on      table_name

example :

           drop   index      uindex     on     users