Let you have Table T1 with fields:
- ID integer
- MSG varchar
And you should do many searches as:
- find records of T1 where ID = constant
Database engine needs to check each row in the table, comparing ID value. If there are many rows and only a few rows match to constant, this is clearly not efficient. Such operation is called 'COLUMN SCAN'. But if database engine maintains index on the ID column, it can use much more efficient algorithms to locate matching rows. For instance, to find first row matching constant in table with million rows, it needs only 20 steps.
A similar approach is used in most non-fiction books: terms and concepts that are frequently looked up by readers are collected in an alphabetic index at the end of the book. The interested reader can scan the index relatively quickly and flip to the appropriate page(s), rather than having to read the entire book to find the material of interest. Just as it is the task of the author to anticipate the items that readers are likely to look up, it is the task of the database programmer to foresee which indexes will be useful.
To use advantage of indexes, developer should ask the DB engine to create index. Also developer can drop an index, if he decide it is not needed.
Once index is created, no future human activity is required: the system will update index when the table is modified and it will use indexes in other operations when it thinks this is efficient.
Operations Affected by Indexes
Operations that benefit from Indexes are:
- Search on Value (WHERE f1 = x; WHERE f1 <> x).
- Search by Range (WHERE f1 BETWEEN x AND; >; >=; <; ⇐ ).
- Join of Tables.
- UPDATE and DELETE SQL commands with search condition.
Operations that can be slowed down because of the need to synchronize indexes with the table:
- Add/Delete/Update row.
Therefore developer should find a “gold middle” in using of indexes.