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:

department
department

employee
employees

dept_emp
dept_emp

 

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.
ref: http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

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

Here we have schema with name employee_schema with following tables;

department
department

employee
employees

dept_emp
dept_emp

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

subquery

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

indexes1

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

Get Indexes in SQL Server

Use below query to get Indexes in the SQL Server

How to remove phpmyadmin permission to access the mysql database?

Output:
Forbidden You don’t have permission to access / on this server.

TO RESOLVE THE ABOVE ERROR WE NEED TO MAKE THE FOLLOWING CHANGES IN phpmyadmin.conf FILE LOCATED AT c:\wamp\alias\phpmyadmin.conf

1. After updating/changing the phpmyadmin.conf file save and exit.
2. Restart the wamp server. Thats it! You can find your server responding now. 

If there is other error the above please look for it on other forums else uninstall the wamp server delete all the files from the installed directory and re-install it.

Good Luck!