When to use MySQL index

 Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
According to MySQL document: " If a table has 1,000 rows, this is at least 100 times faster than reading sequentially"

Indexes are best used on columns that are frequently used in where clauses, jpin, min(), max()and in any kind of sorting, such as "order by". But it will slow down for e it will slow down  MySQL "update" and "insert".

Suppose we have table employees:
CREATE TABLE employees (
ID INT,
name VARCHAR(60) ); 

We can create an index for name in table empoyees:
create index name_index on employees (name);

To see the index created:
 SHOW CREATE TABLE employees;

return
+-----------+------------------------------------------------
-------------------------------------------------------+
| Table     | Create Table
                                                       |
+-----------+------------------------------------------------
-------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `ID` int(11) default NULL,
  `name` varchar(60) default NULL,
  KEY `name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------
-------------------------------------------------------+
1 row in set (0.00 sec)

You will be much faster by using index in name for the following query:
select * from employees where name ='smith';