MySQL INDEX

For improving the speed of operation in a table we use MySQL INDEX. We can create more than one field as a index field in a table. This is basically use for searching process so that it can lookup records very fast.

As it fetches record very fast from the database, during insert and update it takes a lots of time because database needs to insert or update index value as well.

CREATE MySQL INDEX:

CREATE UNIQUE INDEX index_name ON tbl ( column1, column2,…);

Here unique index means that two rows cannot have the same index value.

For using simple index that has the duplicity simply remove the UNIQUE keyword from the query.

You can also create INDEX for ascending or descending order i.e.

CREATE UNIQUE INDEX index_name ON tbl ( column1 DESC, column2 ASC,…);

Altering INDEX after creating a table:

ALTER TABLE tbl ADD INDEX (column1);
ALTER TABLE tbl DROP INDEX (column1);

To check whether table has INDEX or not:

SHOW INDEX FROM tbl;

Read Also: Optimize a MySQL database

MySQL uses indexes for below operations:

• To find the rows which matches a WHERE clause quickly.
• Eliminate rows from consideration.
• Retrieving rows from other tables while performing joins.
• Finding the MIN() or MAX() value for a specific indexed column key_col.
• To sort or group a table.