How indexes help to boost the performance of database queries?

Answer 1

Using indexes helps to increase the performance of different queries:

1. Searching for records matching a WHERE clause. Indexes help queries looking for values inside a range or for a specific value. For example:

SELECT * FROM Items WHERE Price BETWEEN 14 AND 16

2. Sorting records
SELECT * FROM Items ORDER BY Price ASC

With no index, the database will scan the Items table and sort the rows to process the query. However, if we create an index on the Price columnm, it will provide the database with a presorted list of prices.

3. Grouping records

We can use a GROUP BY clause to group records and aggregate values, for example, counting the number of orders placed by a customer. With an index, the database would retrieve the prices in order and be able count the number of products at each price quickly.

4. Maintaining a unique column
Marking a column as a primary key will automatically create a unique index on the column. We can also create a unique index on a column or multiple columns.

Ref: http://odetocode.com/articles/70.aspx