MySQL CREATE INDEX

CREATE INDEX

In MySQL, an index can be created on a table when the table is created with CREATE TABLE command. Otherwise, CREATE INDEX enables to add indexes to existing tables. A multiple-column index can be created using multiple columns.

The indexes are formed by concatenating the values of the given columns.

Indexes help speed up data retrieval by allowing the database management system to quickly locate rows based on the values in the indexed columns. They are particularly useful for columns frequently used in search conditions or joins.

CREATE INDEX cannot be used to create a PRIMARY KEY.

Syntax:

CREATE INDEX [index name] ON [table name]([column name]);

Arguments

Example:

Code:

-- Create an index named "autid" on the "aut_id" column of the "newauthor" table
CREATE INDEX autid ON newauthor(aut_id);

Explanation:

• This SQL statement creates an index on the specified column of the specified table to improve query performance.


• CREATE INDEX is a SQL statement used to create an index on one or more columns of a table.


• autid is the name given to the index being created. You can choose any meaningful name for the index.


• ON newauthor specifies the table on which the index is created.


• (aut_id) specifies the column(s) on which the index is created. In this case, the index is created on the aut_id column of the newauthor table.

MySQL create UNIQUE INDEX

Create UNIQUE INDEX

Using CREATE UNIQUE INDEX, you can create an unique index in MySQL.

Example:

Code:

-- Create a unique index named "newautid" on the "aut_id" column of the "newauthor" table
CREATE UNIQUE INDEX newautid ON newauthor(aut_id);

Explanation:

• This SQL statement creates a unique index on the specified column of the specified table to enforce uniqueness and improve query performance.


• CREATE UNIQUE INDEX is a SQL statement used to create a unique index on one or more columns of a table. Unique indexes ensure that no two rows have the same value in the indexed column(s).


• newautid is the name given to the unique index being created. You can choose any meaningful name for the index.


• ON newauthor specifies the table on which the index is created.


• (aut_id) specifies the column(s) on which the index is created. In this case, the unique index is created on the aut_id column of the newauthor table.


• Unique indexes are particularly useful for enforcing data integrity constraints, such as ensuring that each author ID (aut_id) in the newauthor table is unique.

MySQL create UNIQUE INDEX with index type

Create UNIQUE INDEX with index type

In MySQL, you can specify the type of the INDEX with CREATE INDEX command to set a type for the index.

Example:

Code:

-- Create a unique index named "newautid" on the "aut_id" column of the "newauthor" table using the B-tree index type
CREATE UNIQUE INDEX newautid ON newauthor(aut_id) USING BTREE;

Explanation:

• This SQL statement creates a unique index on the specified column of the specified table using the B-tree index type to enforce uniqueness and improve query performance.


• CREATE UNIQUE INDEX is a SQL statement used to create a unique index on one or more columns of a table. Unique indexes ensure that no two rows have the same value in the indexed column(s).


• newautid is the name given to the unique index being created. You can choose any meaningful name for the index.


• ON newauthor specifies the table on which the index is created.


• (aut_id) specifies the column(s) on which the index is created. In this case, the unique index is created on the aut_id column of the newauthor table.


• USING BTREE specifies the index type. B-tree indexes are the default index type in MySQL and are well-suited for most use cases. They allow for efficient data retrieval and are particularly useful for range queries and sorting operations.

Previous: MySQL Constraintsr
Next: MySQL ALTER TABLE