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

Explain Keyword and SQL Execution Plan in MySQL

SQL Execution Plan: You can see the SQL Execution Plan by using “Explain” keyword. It tells how query is executed.

Understanding the Query Execution Plan in MySQL
Depending on the details of your tables, columns, indexes, and the conditions in your WHERE clause, the MySQL optimizer considers many techniques to efficiently perform the lookups involved in an SQL query. A query on a huge table can be performed without reading all the rows; a join involving several tables can be performed without comparing every combination of rows. The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known as the EXPLAIN plan. Your goals are to recognize the aspects of the EXPLAIN plan that indicate a query is optimized well, and to learn the SQL syntax and indexing techniques to improve the plan if you see some inefficient operations.

To explain this lets take the example of simple employee department relationship.

Here we have schema with name employee_schema with following tables;




Now lets join 3 tables employee, dept_emp and department with a where clause

and the output will be
explain with where clause

Understanding the output
* Output contains information of each table involve in the query in a form of a row. Means a row gives information of a table.
* Output is in ordered form, means output is in the order that MySQL would read them while processing the statement

Column 1: (id)
“id” is the SELECT identifier. This is the sequential number of the SELECT within the query. But what that means?

To understand this let’s do some modification in the query


Column 2: (select type)
As its name suggest it give you the type of select statement. Depending on the query select type can be following

Column 3: (table)
This column shows the table to which the row of output refers.

Column 4: (type)
This column tells the type of join. All join types

Column 5: (Possible keys)
This column show the all possible keys that can be used by the MySQL to process the query within the table.

Column 6: (key)
Actually used key from the all possible keys

Column 7: (key_len)
Give the length of the key that is used in column 6.
The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses.

Column 8: (ref)
The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

Column 9: (rows)
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.

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

Trigger in MySQL

In this post we will see how to use Trigger in MySQL.

What is a Trigger:
A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event that is associated with a table occurs e.g., insert, update or delete.

How to create trigger
Example one :





step-5 (this step will create a trigger)


step-7 (now when you insert values in attendance then those values also copy in attendance_1)


now you see you are updating the value only in table attendance but it also automatically updated in table attendance_1.

Note:- above example is useful when only one command is use in trigger. Like in above example we use “insert into attendance_1 values (new.idattendance);”
so if we want to use more then one command in trigger then we have to use delimiter. Example of delimiter is  given below.

Example two :





step-4 ( create delimiter for multiple statement)

Note: you can add multiple statement in
and for this you have to use delimiter.