A common database has tables which often need to be queried based on a certain column. For example, the booktown database has an invoice table with the following structure:

Table 5-1. Invoice Table

Col Name Description Type
prod_id product identifier integer
descript description of product text
amt_stock number of products in stock integer

The booktown company daily performs a search to find products based on the product identifier (prod_id). One of the most commonly used search conditions are:

  SELECT amt_stock FROM invoice WHERE prod_id = 7280;
   

When the database performs the above command, it scans every row in the invoice table to find a result that returns true. As you can see, this can take a long time if the table is large, and contain only a few entries with product id 7280.

The solution to this problem is to use an index. The logic behind an index is similar to an index within a book. If you were looking for a particular term in a book, the best way to search for it is to look in the index. This way, you can find the term without having to read the entire book. All you need to do is scan through the index and skip to the place where the term is listed.

When the author writes that book, they have to anticipate which terms must be included in the index, and which term should be excluded from the index. Likewise, as the database programmer or administrator, you will need to anticipate which table columns should be indexed, and which shouldn't.

Therefore, the best way to perform faster searches using product identifier is to create an index on the prod_id column of the invoice table. Use the following command:

Example 5-1. Creating an Index

  CREATE INDEX prod_id_index ON invoice(prod_id);
   

This index is automatically used by the system. The system may decide to use this index if it is more efficient than a sequential table scan. It makes this decision based on the VACUUM ANALYZE command. You can run the VACUUM command to update the statistics stored.

You can also remove an index from the database using the DROP INDEX command. Using our product identifier index, we can drop it with:

Example 5-2. Dropping an Index

  DROP INDEX prod_id_index;
   

Note Key Difference
 

This is where indices and primary keys differ. Indices can be created or destroyed at any time. Primary keys can only be created during table creation and destroyed when the table is removed from the database.