What are the different types of indexes, what are the benefits of each?

Question

What are the different types of indexes, what are the benefits of each?

I heard of covering and clustered indexes, are there more? Where would you use them?

Solution 1

• Unique - Guarantees unique values for the column(or set of columns) included in the index
• Covering - Includes all of the columns that are used in a particular query (or set of queries), allowing the database to use only the index and not actually have to look at the table data to retrieve the results
• Clustered - This is way in which the actual data is ordered on the disk, which means if a query uses the clustered index for looking up the values, it does not have to take the additional step of looking up the actual table row for any data not included in the index.

Solution 2

OdeToCode has a good article covering the basic differences

As it says in the article:

Proper indexes are crucial for good performance in large databases. Sometimes you can make up for a poorly written query with a good index, but it can be hard to make up for poor indexing with even the best queries.

Quite true, too... If you're just starting out with it, I'd focus on clustered and composite indexes, since they'll probably be what you use the most.

Solution 3

I'll add a couple of index types

BITMAP - when you have very low number of different possible values, very fast and doesn't take up much space

PARTITIONED - allows the index to be partitioned based on some property usually advantageous on very large database objects for storage or performance reasons.

FUNCTION/EXPRESSION indexes - used to pre-calculate some value based on the table and store it in the index, a very simple example might be an index based on lower() or a substring function.

Solution 4

PostgreSQL allows partial indexes, where only rows that match a predicate are indexed. For instance, you might want to index the customer table for only those records which are active. This might look something like:

create index i on customers (id, name, whatever) where is_active is true;

If your index many columns, and you have many inactive customers, this can be a big win in terms of space (the index will be stored in fewer disk pages) and thus performance. To hit the index you need to, at a minimum, specify the predicate:

select name from customers where is_active is true;

Solution 5

Conventional wisdom suggests that index choice should be based on cardinality. They'll say,

For a low cardinality column like GENDER, use bitmap. For a high cardinality like LAST_NAME, use b-tree.

This is not the case with Oracle, where index choice should instead be based on the type of application (OLTP vs. OLAP). DML on tables with bitmap indexes can cause serious lock contention. On the other hand, the Oracle CBO can easily combine multiple bitmap indexes together, and bitmap indexes can be used to search for nulls. As a general rule:

For an OLTP system with frequent DML and routine queries, use btree. For an OLAP system with infrequent DML and adhoc queries, use bitmap.

I'm not sure if this applies to other databases, comments are welcome. The following articles discuss the subject further:

• Bitmap Index vs. B-tree Index: Which and When?
• Understanding Bitmap Indexes

View more solutions