Indexing in Database

indexes are used to speed up the data retrieving queries known as select queries. Indexes are created on a per column basis. If you have a table with the columns: name, age, birthday and employeeID and want to create an index to speed up how long it takes to find employeeID values in your queries, then you would need to create an index for employeeID. When you create this index, MySQL will build a lookup index where employeeID specific queries can be run quickly. However, the where clause based on the  name, age and birthday queries would not be any faster.

Indexes are something extra that you can enable on your MySQL tables to increase performance,but they do have some downsides. 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.

Advantages 

• Indexes speed up the data retrieving based on where and order by clauses

Disadavantages

•  Indexes slow down inserts , update and deleted, so you want to use them carefully on columns that are FREQUENTLY changed.
•  Creating an index requires an additional storage space . this space is occupied by the look-up index and it keep a track of modification(insert/delete/update) records those were done to the related column/field. therefore too many indexes can increase the database size and the file size unnecessarily.

create mysql index on new table

 If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).

CREATE TABLE employee (
 name VARCHAR(50), 
 employeeID INT, INDEX (employeeID)
)

create mysql index on existing table

CREATE INDEX id_index ON employee(employeeID)

Hope this will helpful for you!

regards

Chathuranga Tennakoon

chathuranga.t@mail.com