Indexes in Database: Top Rules

An index is a database object. That uses to speed up the retrieval of tuples from base relations. 

The index is independent of the table it indexes. It stores address pointers to tuples of the base relation. 

Once created, indexes are maintained by the DBMS automatically and used to service certain user access requests.

The software engineer’s (or DBA’s) responsibility is to create them. Check out here how to create an index.

IN THIS PAGE

  1. An index you can create when
  2. An index is not necessary when

An index you can create when

  • A column or combination of columns that use very often in system query conditions (join conditions).
  • A column contains a wide range of values.
  • A column contains a large number of null values.
  • The table is large and most SQL queries expect to retrieve less than 2 % – 4% of the tuples.
  • A column or combination of columns is frequently used in the Order-By clause of queries.

An index is not necessary when

  • The table is small.
  • Most queries are expected to retrieve more than 2% – 4% of the tuples.
  • The table is frequently updated (there are exceptions to this rule).
  • Columns are not often used in database queries.

Related

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.