Question

What is an index. Give an example of table that might have an additional index other than the keys?

Answer 1

In database systems, an index is a data structure which improves the speed of data retrieval from database. The downside is that having an index require additional writes and additional storage for maintaining index structure.

In a relational database, a table may require additional indexes other than keys when those attributes are often used to select or filter out records. This is especially required if table volume is high. For example, EMPLOYEE table (which contain information about employees of a company) may have social security number (ssn) as primary key and thus we have a key index on ssn. However, attribute Department_ID (which represent the department to which the employee belong) is often used in queries to filter out employee records based on particular departments. Then it makes sense to have index on Department_ID as well.