In MySQL, we can create indexes on one or more columns of a table to improve query performance.
Indexes help the database engine locate and retrieve rows more efficiently.
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.
CREATE INDEX index_name ON table_name (column1, column2);
"index_name" is the name of the index.
"table_name" is the name of the table on which the index is being created
(column1, column2) specifies the columns on which the index is created.
Single Column indexes a single column of a table. It speeds up searches for values in that column.
CREATE INDEX index_product_name ON product (index_product_name);
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.
CREATE INDEX index_product_name_price ON products (product_name, price);
If we want to enforce uniqueness on the indexed columns, we can create a unique index:
CREATE UNIQUE INDEX index_unique_product_code ON products (product_code);
FULLTEXT index is a type of index that is specifically designed for searching text-based data efficiently.
CREATE FULLTEXT INDEX product_description ON products (product_description);