NeilSys

The index makes query execution(where, order) faster because it is stored in memory, the corresponding index row can be looked at and it contains a pointer to where the actual data is stored. So MySQL can go to the exact location in the table without having to scan the table

Basically an index is a map of all keys that is sorted in order. With a list in order, then instead of checking every key, it can do something like this:

1: Go to middle of list – is higher or lower than what I’m looking for?

2: If higher, go to halfway point between middle and bottom, if lower, middle and top

3: Is higher or lower? Jump to middle point again, etc.

Using that logic, you can find an element in a sorted list in about 7 steps, instead of checking every item.

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 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. You won’t be updating the employee’s name very often, so you don’t have to worry about a performance hit there.

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.