When people try to adjust a database for performance, a big part of the discussion is the creation of indices.
A basic concept of SQL databases is that the data has no order.
The advantage of this lack of order is that it allows the RDBMS to INSERT, UPDATE, and DELETE rows quickly.
The disadvantage is that finding data in rows using searching and sorting is slow (but it will work). Searching
a small database is easy, but searching a large production database with millions of rows can be slow.The RDBMS system provides the INDEX as a way of solving this problem. Basically, an index is a sorted file of values with pointers to the physical locations of the indexed data. When you request a specific value, this value is located in the sorted index and the values can be retrieved using the pointers to the rows of matching data. This way, the RDBMS system does not need to read the entire table to retrieve only those matching rows. The trade-off is that data retrieval is much faster, but INSERT, UPDATE, and DELETE are slower because the index must be updated. Each RDBMS system handles indices differently.

An index is automatically created for a PRIMARY KEY without the user’s knowledge. Automatic creation of an index for a FOREIGN KEY or constraint such as UNIQUE depends upon your RDBMS system. Once an index is created, its use is invisible to the user. No changes are made to any SELECT, INSERT, UPDATE, or DELETE statements. Creation or removal of an index only affects performance.
You may wish to create an index for:

          WHERE      Search data
          ORDER BY   Sorted data
          GROUP BY   Grouped data
          JOIN       Joined data