Index for Database Tables
Efficiency in Database Design – Part 2
Division 3
Forward: In this part of the series, we look at what is called, Index in database tables.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
What is an Index?
An index is a DBMS object that can be set on one or a group of columns of a table. It can be set on a single column, such as a single primary key; it can be set on a group of columns, such as a composite primary key. You can have more than one index for a table.
Uses of Index
Index can be classified as two types: normal index and unique index. Assume that you have a table already stored in a database. You can make the DBMS display the table data on the screen the way it was recorded. However, this is usually not what you will do. You will usually be displaying your table in a sorted order (alphabetically), based on a column. If the normal index is set on the column on which the sorting will be based, then the table data will be retrieved faster then if the index was not set.
An index has a lot of information about your table. Assume that you want to retrieve only a particular row: You need to identify that row with the table primary key value for that row. If an index has been set on the primary key column, then the row will be retrieved faster than if no index was set.
You can program your DBMS to link your tables in the database (disk) by certain columns (especially keys). You may want to display data from the two tables, based on the linked columns. If these linked columns were index, then the retrieval of the data would be faster than if they are not indexed.
A primary key has to be unique. However, if the user decides to type in two of the same values in the primary key column, what will you do? You can prevent this. To prevent this, set the unique index on primary key column of your table. You can also use the unique index to force any other column to be unique.
You DBMS documentation will tell you how to set index on a column. You can set an index on any column. If you want your database to operate fast, then set indexes on all your tables.
- Maintains uniqueness of columns especially keys.
- Speeds data retrieval on indexed columns.
- Ordering of rows.
- Speeds joining of tables on indexed columns
Disadvantages of Indexing
- Uses extra disk space.
- Many indexes slow down updating process of the table
- For small (short) tables there is no gain in speed.
Advice
So, as a database designer which columns should you index? Should you index at all? There is no exact solution for this. Take my advice as follows:
- Define a unique index on a primary key.
- Index all foreign keys.
- Want to insist on unique values for a column, index that column.
- Index frequently searched columns.
- Index columns that frequently determine the sort order.
- The longer the table, the likelihood that it should have indexes.
That is it for database indexes. We take a break here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT