MySQL Indexes

MySQL Indexes

There are three types of MySQL indexes: UNIQUE, FULLTEXT and SPATIAL.
A UNIQUE index means that all values in the index must be distinct.
FULLTEXT indexes are available only for InnoDB and MyISAM tables and can include columns with data type like: CHAR, VARCHAR and TEXT.
SPATIAL indexes are available only for MyISAM and InnoDB tables, indexed columns must be NOT NULL and the full width of each column is indexed.

MySQL Create Index syntax

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name
[ USING { BTREE | HASH } ]
ON table_name (column_name,…)

Create UNIQUE index example

CREATE UNIQUE INDEX test_index
    ON test (id, name);
	
ALTER TABLE test ADD PRIMARY KEY (id);	

Output

unique index TEST_INDEX created.
table TEST altered.

Drop index example

ALTER TABLE `test` 
DROP INDEX test_index;

Output

table `test` altered.