How to use indexes to get better performance – MySQL

It is very important to have a close look on the indexes because inappropriate use of indexes may lead to performance degradation.

Here are some points that tells how:
1. Indexes use extra space in database to get stored.
2. Indexes slow down inserts, updates and delete operations because indexes update every time after the these operations.


First Confusion:

After reading the above point you will think why and when to use indexes if degrades the performance?
Simple answer to this question is, when you have “where” clause and “joins” in your query, then, indexes on the proper columns should be in order to optimize the performance of the query.


Second Confusion:

Now, even if you have “where” clause in insert, update and delete query updation of the table is required based on the indexed column and it will slow down the performance. So, we should still use the indexes on the column?
You have to analyse the output to identify that you need index on particular column or not.
Updates and Deletes require finding the relevant row(s), so they benefit from indexes for that step, but then if you have to many indexes, even that benefit can be negated which means there should a balance.

There are two things that are important and you need to know before applying indexing on the columns in the table:
1. Query Execution Plan
2. Optimize the query with the help of output of Query Execution Plan


Let’s learn this with an example. We have used 3 tables in the below example:





1. Query Execution Plan See post of Query Execution Plan
If you know what is query execution plan, then proceed to 2nd step

Now lets join 3 tables employee, dept_emp and department without using where clause

We will get the following output (OUTPUT 1)
explain without where clause

Now we will use where clause with the same query

and the output will be (OUTPUT 2)
explain with where clause

Now observe the two outputs
In OUTPUT 1 we didn’t use where clause so in first case MySQL evaluated the 4 rows i.e all rows present in the table. where as in OUTPUT 2 We have where clause so MySQL evaluated only one row.
So while writing join query try to use where clause on the first table i.e. “e” in our case and assign an index to this column which make the search faster. If you are using column with primary key then you dnt have to assign any index as implicit index is assign to it by MySQL.
Expert says type column in first row in the output table should not contain “ALL” so our first query without where clause is not a good query and second one is good.

The post was all about “How to use indexes to get better performance – MySQL”.

Introduction to Indexes in database

Q: What are indexes?
Indexes are the special tables in the database that makes the search faster.
The users cannot see the indexes, they are just used to speed up searches/queries.

Q: How indexes reads the data faster?
Indexes allow to find data faster without reading the whole table as it stores entries in sorted order.
Database maintains index just like as index present in the book. Index hold the index key and reference which points to the table row. Indexes use B-tree(Balanced Search Tree) data structure to make the search faster with minimum amount of disk reads.

Index is assign to Primary key as soon as you creates a table with primary key. These are called Implicit Indexes


Note: If you assign a primary key to a column then index is automatically assign to it.
Some kind of an index is required to police the PRIMARY KEY, otherwise it would require scanning the whole table on each insert (to ensure uniqueness).

Explicit Indexes

You can also assign index to a column while creating table using following query

Once the table is created, you can assign index using following query

To drop index

But be careful while using indexes. Inappropriate use of indexes may lead to performance degradation.
See how to use indexes to get better performance