When and How To Use Indexes In Mysql ?

This comes up in discussions almost every new project I work on, because it's a very important thing to consider when designing a database. When deciding when and how to create an index in your MySQL database, it's important to consider how the data is being used.

Let's say you have a database of employees. We will create it like this:

CREATE TABLE employees (
ID INT,
name VARCHAR(60),
salary decimal(10,2),
date hired(date)
)

So you will notice that this table is pretty simplistic, and doesn't really contain all the info you would need to actually manage employees, but its just for the sake of demonstration, and you could always add more later, or even make another table and use joins if you had really complex needs.

For now we will go over these real quick.

The ID is basically just a number (INT) which can hold a very large number. If this were real world I would probably make it unsigned, since you will never have a negative employee ID – but either way, you will never reach the number of employees it would take to get to the number that would fill up an INT.

Even unsigned int will hold values up to 2,147,483,647. So if you have 2 billion employees, you would probably not be a developer anymore ;-).

You might want to consider making the field an auto increment, and primary key, the auto increment depending on how data will be entered into this database.

Name is a simple varchar(60) which should cover most people's names.

Salary is a decimal with 10 total digits, two on the right hand side of the decimal point. This would handle a salary of up to 99,999,999.99 – again, you're not likely to hit this limit.

Date hired will be a date in this format 2010-05-06. YYYY-MM-DD.

So when considering this simple table, where would you expect to need an index?

If we assign ID as a primary key, we don't need one there.

Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by".

You should also pay attention to whether or not this information will change frequently, because it will slow down your updates and inserts. Since you wont frequently be adding employees, you don't have to worry about the inserts.

Let's say that you will be looking up the employees with a php web interface and the end user will be typing in the employees name to find them, since remembering the employee ID's would be cumbersome.

It sounds like this situation would be good to use an index.

A – You won't be updating the employee's name very often, so you don't have to worry about a performance hit there.

B – You WILL be using the employee in where clauses like this:

select * from employees where name ='smith';

C – You WILL be generating reports, which will probably be alphabetic, like this:

select * from employees order by name asc;

So in this simple example it's easy to see when it would be important to use indexes.
So, you could do it like this:

create index name_index on employees (name);

You might be working on a more complex database, so it's good to remember a few simple rules.

- Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.
- Indexes speed up where clauses and order by.

Remember to think about HOW your data is going to be used when building your tables.

There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan. Indexes really only come in handy with tables that have a lot of rows.

So, if Joe’s Pet Shop was using this database, they would probably be able to leave the index off the "name" column.

If Microsoft was using this database (hah!) they might want to throw and index in there.

Another thing to remember, that is a con in the situation of our employees database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently.

As you can see there are many things to consider with indexes, even with a very simple table as this.

Source: http://www.howtoforge.com/when-to-use-indexes-in-mysql-databases