Database Indexing Techniques

Indexes are database objects associated with database tables and created to speed up access to data within the tables. Indexes are part of constraint mechanism. When we insert data in table and if a column has primary key constraint, then database checks if row with the same primary key value exists. If it doesn’t then the full table is scanned, An index gives immediate access to key values , so that check for existence can be made virtually instantaneously.

A unique constraint also requires an index. Column of the unique constraint can be left Null unlike primary key constraint. In database schema tables have parent child relationship, with primary key on parent table and foreign key on child table. If indexes are applied on foreign key then retrieval of data becomes faster, deletion of data also speeds up.