What is a Database Index?

Mike Wolfe -
Original table

An index is an internal copy of a table sorted by the index field(s).  The only columns are the index field(s) and primary key.

The internal table maintained for the CityName index.

The database uses the index to look up the primary key.  It uses the primary key to look up the row in the original table.

Advantages

  • Faster lookups

Disadvantages

Each index is an extra table for the database to maintain, which means:

  • Extra storage space
  • Slower INSERT/UPDATE/DELETE

Bottom Line

It's important to understand the tradeoffs when adding (or omitting) indexes.

Image by kropekk_pl from Pixabay