MySQL Database INDEX Example Tutorial

MySQL Database Index Example Tutorial 

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

While creating index it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.

Practically, Indexes are also type of tables which keeps primary key or index field and a pointer to each record in to the actual table.

The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search Engine to locate records very fast.

INSERT and UPDATE statements takes more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to inert or update index values as well.

An example of a index 

CREATE INDEX index_name ON table(column);

To see the index on the table

SHOW INDEX FROM table;

To remove the index from the table.

DROP INDEX column ON table;

• The primary key is implicitly indexed for both MyISAM and InnoDB
• Indexes are created on per column basis
• Indexes can slow down INSERT(s) and UPDATE(s)
• Indexes increase performance on ORDER BY and WHERE clauses.