Introduction to indexes

An index or a database index helps with returning the query results quickly, by avoiding full table scans. An index can be created for a specific table and can include one or more keys. Keys refer to the columns in the table. However, there will be extra space used to keep a separate sorted copy of indexed columns.

Let's take a simple example and see how an index works.

Consider a population table with the following columns and some sample values:

Figure 6.1 – Population table

Now, let's say you just want to retrieve the list of populations for specific continents, for example:

SELECT population_in_millions, country FROM population WHERE continent = "Asia";

Here, in order to find rows 1 and 4, which are countries in Asia, you would have to iterate through each of the rows in the table, which is called a full table scan.

Now, if you want to avoid a full table scan, you can create an index...