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 database uses the index to look up the primary key. It uses the primary key to look up the row in the original table.
- Faster lookups
Each index is an extra table for the database to maintain, which means:
- Extra storage space
- Slower INSERT/UPDATE/DELETE
It's important to understand the tradeoffs when adding (or omitting) indexes.