Syntax\n------\n\nCREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL|VECTOR] INDEX \n  [IF NOT EXISTS] index_name\n    [index_type]\n    ON tbl_name (index_col_name,...)\n    [WAIT n | NOWAIT]\n    [index_option]\n    [algorithm_option | lock_option] ...\n\nindex_col_name:\n    col_name [(length)] [ASC | DESC]\n\nindex_type:\n    USING {BTREE | HASH | RTREE}\n\nindex_option:\n    [ KEY_BLOCK_SIZE [=] value\n  | index_type\n  | WITH PARSER parser_name\n  | COMMENT 'string'\n  | CLUSTERING={YES| NO} ]\n  [ IGNORED | NOT IGNORED ]\n  | DISTANCE={EUCLIDEAN| COSINE} ]\n  | M=number ]\n\nalgorithm_option:\n    ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}\n\nlock_option:\n    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}\n\nDescription\n-----------\n\nThe CREATE INDEX statement is used to add indexes to a table. Indexes can be created at the same as the table, with the CREATE TABLE statement. In some cases, such as for InnoDB primary keys, doing so during creation is preferable, as adding a primary key will involve rebuilding the table.\n\nThe statement is mapped to an ALTER TABLE statement to create indexes. See ALTER TABLE. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead.\n\nIf another connection is using the table, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.\n\nAnother shortcut, DROP INDEX, allows the removal of an index.\n\nFor valid identifiers to use as index names, see Identifier Names.\n\nFor limits on InnoDB indexes, see InnoDB Limitations.\n\nNote that KEY_BLOCK_SIZE is currently ignored in CREATE INDEX, although it is included in the output of SHOW CREATE TABLE.\n\nPrivileges\n\nExecuting the CREATE INDEX statement requires the INDEX privilege for the table or the database.\n\nOnline DDL\n\nOnline DDL is supported with the ALGORITHM and LOCK clauses.\n\nSee InnoDB Online DDL Overview for more information on online DDL with InnoDB.\n\nCREATE OR REPLACE INDEX\n\nIf the OR REPLACE clause is used and if the index already exists, then instead of returning an error, the server will drop the existing index and replace it with the newly defined index.\n\nCREATE INDEX IF NOT EXISTS\n\nIf the IF NOT EXISTS clause is used, then the index will only be created if an index with the same name does not already exist. If the index already exists, then a warning will be triggered by default.\n\nIndex Definitions\n\nSee CREATE TABLE: Index Definitions for information about index definitions.\n\nWAIT/NOWAIT\n\nSet the lock wait timeout. See WAIT and NOWAIT.\n\nALGORITHM\n\nSee ALTER TABLE: ALGORITHM for more information.\n\nLOCK\n\nSee ALTER TABLE: LOCK for more information.\n\nProgress Reporting\n\nMariaDB provides progress reporting for CREATE INDEX statement for clients that support the new progress reporting protocol. For example, if you were using the mariadb client, then the progress report might look like this::\n\n``sql\nCREATE INDEX i ON tab (num);\nStage: 1 of 2 'copy to tmp table'    46% of stage\n`\n\nThe progress report is also shown in the output of the SHOW PROCESSLIST statement and in the contents of the information_schema.PROCESSLIST table.\n\nSee Progress Reporting for more information.\n\nWITHOUT OVERLAPS\n\nThe WITHOUT OVERLAPS clause allows you to constrain a primary or unique index such that application-time periods cannot overlap. It can be used like this:\n\n`sql\nCREATE UNIQUE INDEX u ON rooms (room_number, p WITHOUT OVERLAPS);\n`\n\nWITHOUT OVERLAPS` is not available.\n\nExamples\n--------\n\nCREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-index