MySQL INDEX

MySQL INDEX

A database index is a data structure that improves the speed of operations in a table.Indexes are also can be considered as a 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 or use the indexes defined on tables , they are used by Database Search Engine to locate records very fast.

When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.

* Allow the server to retrieve requested data, in as few I/O operations
* Improve performance
* To find records quickly in the database

for example


CREATE TABLE sampletable (id INT, fname VARCHAR(50), lname VARCHAR(50), INDEX (id))

Simple and Unique Index:
in this type of indexing two rows cannot have the same index value

syntax :CREATE UNIQUE INDEX ON ( column1, column2,...);

CREATE UNIQUE INDEX F_INDEXON tab1 (tab2)


Points to consider for optimizing the MySQL Indexes

* The columns with the most unique and variety of values should be used.
* Smaller the index better the response time.
* For functions that need to be executed frequently, large indexes should be used.
* Avoid use of index for small tables.


Good Luck :)