- A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.
- Indexes can be created using one or more columns of a database table.
- Providing the basis for both rapid random look ups and efficient access of ordered records.
- The disk space required to store the index is typically less than that required by the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table).
- The users cannot see the indexes, they are just used to speed up searches/queries.
- Indexes may be defined as unique or non-unique.
Creates an index on a table. Duplicate values are allowed.
CREATE UNIQUE INDEX index_name ON table_name (column_name)
Creates a unique index on a table. Duplicate values are not allowed.
How does a database index work?
Database Index Tips
- An index is sorted by key values, (that need not be the same as those of the table).
- is small, has just a few columns of the table.
- refers for a key value to the right block within the table.
- speeds up reading a row, when you know the right search arguments.
Database Index Tips
- Put the most unique data element first in the index.
- Keep indexes small. The smaller the index, the better the response time.
- For small tables an index is disadvantageous.
- An index slows down additions, modifications and deletes. It's not just the table that needs an update, but the index as well.
