How to do MySQL Indexing

MySQL Indexes! Whooh! Let me tell you what my experience about indexing a table. I had a SQL query which took 8-9 secs to fetch the data I want. After indexing now it only takes 1 sec. Interesting right. Let’s go through what’s indexing and how to and when to and stuff. You could find technical explanation of indexes in the internet. So let me elaborate it in a more appealing way to you.

Index is a pointer to the table and it allows you to retrieve the data faster. It’s the same as the index which we find in the back of a book. Index will represent the book in the keyword or subject order where book is printed in the page order. It’s true that index will faster your select query but it will slow down your other queries such as updates and inserts. You might be wondering why is that. It’s because once the new data is added to the table, Index should be updated too. Otherwise how can an index points to the new data in the table.

There are three types of indexes

  • Single Column(EG :- Indexed by mobile No)
  • Composite(EG :- Indexed by first name and last name)
  • Unique (EG :- indexed by e-mail)

Look at table below.

ID  First Name Last Name   Mobile No E-Mail
1 Dasun Hegoda 0773525252
2 Abbel Perera 0773528552
3 Zohi Mark 0773958552
4 James Parker 0779628552
5 Zxon Abe 0779558552


!Important : Note that primary key(ID) will be automatically indexed when you create the table.


If you index above table by e-mail, it would look like this.

E-Mail  ID 2 1 4 3 5


Now when we query(select * from table where e-mail =”####”) by e-mail, data will be retrieved faster.

Indexes make data retrieval faster and efficient but indexes make data modification slower. So you might be wondering when should I create a index. You should create indexes on columns that are frequently used in WHERE clauses. Make sure that columns you select are highly unique. EG :- Don’t create an index on gender column because it only contains male and female. Don’t create indexes on small tables(less than 8,000 rows). Don’t build an indexes on columns which are frequently updated because every time you update the data indexes should be updated too.

Okay now let’s see how to add an index

ALTER TABLE table_name ADD PRIMARY KEY (column/column_list)
ALTER TABLE table_name ADD UNIQUE index_name (column/column_list)
ALTER TABLE table_name ADD INDEX index_name (column/column_list)
CREATE INDEX index_name ON table_name (column/column_list);

Also you can remove the index whenever you want.

DROP INDEX index_name ON table_name

You will find indexing really useful if you use it the right way, If you mess up I don’t have to say and you know it.



Leave A Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.