Create Index In MySQL

In MySQL, we can create indexes on one or more columns of a table to improve query speed.

Indexes help the database engine to locate and retrieve records or rows more efficiently.

The drawback of using Indexes is that it increases the storage space of the database and potentially reduces the speed of write operations in the Table.

Types of Indexes in MySQL:

Single-column index: An Index have been created through single column.
Composite or Multiple index: We can create composite or Multiple columns index in the MySQL Table.
Unique index: It ensures that all rows in the table have uniqueness by using the Unique index on records.
Full-text index: It used for full-text searches in text columns.
Spatial index: Used for spatial data types.

Create Index Syntax In MySQL Table:

An index in a relational database is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

Example:

CREATE INDEX indexName
ON tableName (column1, column2);

`indexName` is the name of the index.
`tableName` is the name of the table on which the index is being created
`(column1, column2)` specifies the columns on which the index is created.

DROP INDEX from MySQL Table:

using the below command, we can DROP INDEX from SQL Table.

Example:

DROP INDEX indexName;

Types Of Indexes In MySQL:

Single Column Index In Table:

Single Column indexes a single column of a table. It speeds up searches for values in that column.

Example:

CREATE INDEX index_product_name
ON product (index_product_name);

Composite or Multiple Column Index In Table:

Multiple Columns also known as a compound index, this type of index is created on multiple columns. It can speed up queries that filter, sort, or join based on combinations of these columns.

Example:

CREATE INDEX index_product_name_price
ON products (product_name, price);

Unique Index In Table:

If we want to enforce uniqueness on the indexed columns, we can create a unique index:

Example:

CREATE UNIQUE INDEX index_unique_product_code
ON products (product_code);

Full-Text Index (for search in data) In Table:

FULLTEXT index is a type of index that is specifically designed for searching text-based data efficiently.

Example:

CREATE FULLTEXT INDEX product_description
ON products (product_description);

Spatial Index In Table:

A Spatial index is a special type of index used to enhance the performance of spatial queries.

Spatial index is essentially used on data types, such as points, lines, and polygons. It helps retrieve geographic information systems (GIS) and applications involving location-based data.

Example:

CREATE SPATIAL INDEX indexName ON tableName (spatialColumn);

<-- Default Key

Auto Increment -->