Table 5-1. Invoice Table
|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.
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.
Example 5-2. Dropping an Index
DROP INDEX prod_id_index;
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.