How are Indexes used by Database Engine?

In the previous lesson, you saw that indexes are created to improve performance.Given the sample table and the index that has been placed on it, you might issue a quick query as follows:

SELECT Lastname from BasicTable 

You will get back the Lastname contents in random order.
They will not be sorted or indexed. Why is this?
Well, you need to add a clause to your SQL statement. You need to indicate that you want the information to be sorted on the Lastname column.
You do this, as you will later see, with the ORDER BY clause:

SELECT Lastname from BasicTable 
ORDER BY Lastname 

When you do this, the engine can use the index, read down through the associated row pointers, and retrieve the information to fulfill your request. The index is really a set of pointers to the information in your tables.
What happens if there is no index? That is a good question. The engine will still return the rows in order, but it will take additional processing on the server to retrieve and sort the rows that match your request.

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
They also organize the way in which the database stores data. A good real-world example is a book, much like this one, that has an index at the back that helps you find where the important information is. It saves a lot of flicking through the pages randomly trying to find the topic you want.
Question: Given that an index helps speed up SELECT queries and WHERE clauses, why not always have one? First of all, while it speeds up data retrieval, it slows down data input, with UPDATE and INSERT statements, for example. Additionally, it adds to a database's size, though it is a consideration. Using indexes is good practice, but it is best done judiciously. For example, using them to help your most common queries. Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order. Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index. The basic format of the statement is as follows:

CREATE INDEX < index_name>
ON <table_name> (<column_names>)

CREATE INDEX member_name_index
ON MemberDetails (FirstName, LastName);

SELECT FirstName, LastName
FROM MemberDetails;

Click the Quiz link below to take a quick quiz about database underpinnings.
Database-Foundations - Quiz
In the next lesson, we will look at how you insert information into the database.