Basics of Indexes in Database Systems

(A) What is an index ?

1. An index is a physical list of values [tuple to be specific in
R-DBMS] that a table contains.
2. It occupies physical space in the database and is as real as the
table and is different from the table [ though a user can't
directly can't reach the index (which could be a table/tree in
itself) ]
3. Hence if you create an index on table1.create_date, for example,
then a file will be created that consists of one row for every
row in table1. Each row contains the create_date and a pointer
back to the row in table1 that the index's row belongs to.
4. Every time you update, delete from, or insert into table1, the
index is also updated, deleted from, or inserted into.

Optimizing our Indexes

1. Creating indexes on char and text fields is not really a good
idea; they work best on fixed length number fields.
2. Every index increases the time in takes to perform INSERTS,
UPDATES and DELETES, so the number of indexes should not be very
much. Try to use maximum 4-5 indexes on one table, not more. If
you have read-only table, then the number of indexes may be
increased.
3. Keep your indexes as narrow as possible. This reduces the size
of the index and reduces the number of reads required to read
the index.
4. If you create a composite (multi-column) index, the order of the
columns in the key are very important. Try to order the columns
in the key as to enhance selectivity, with the most selective
columns to the leftmost of the key.
5. If your application will be performing the same query over and
over on the same table, consider creating a covering index on
the table.
6. Clustered indexes are more preferable than non-clustered, if you
need to select by a range of values or you need to sort results
set with GROUP BY or ORDER BY.

(B) What is a clustered index ?

1. A clustered index is a type of index that re-orders the way
records in the table are physically stored.
2. A table can have only one clustered index.
3. The leaf nodes of a clustered index (almost all the times)
contains actual data pages.
4. By default a clustered index a created with a primary key.

(C) What is a non-clustered index ?

1. A non-clustered index is a type of index in which the logical
order of the index doesn't matches the physical stored order of
the rows in the disk.
2. A table can have more than one non-clustered index.
3. The leaf nodes of a clustered index doesn't contain actual data
pages
4. By default a non-clustered index a created on a unique key

Wish you a happy learning.

Thanks !!!