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...