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”.