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

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?

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.

Different database systems have different names for the same type of index, so be careful with this. For example, what SQL Server and Sybase call "clustered index" is called in Oracle an "index-organised table".

To view the types of indexes and its meaning visits: https://msdn.microsoft.com/en-us/library/ms175049.aspx

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.

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;

I suggest you search the blogs of Jason Massie (http://statisticsio.com/) and Brent Ozar (http://www.brentozar.com/) for related info. They have some post about real-life scenario that deals with indexes.

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

SQL Server 2008 has filtered indexes, similar to PostgreSQL's partial indexes. Both allow to include in index only rows matching specified criteria.

The syntax is identical to PostgreSQL:

create index i on Customers(name) where is_alive = cast(1 as bit);

Oracle has various combinations of b-tree, bitmap, partitioned and non-partitioned, reverse byte, bitmap join, and domain indexes.

Here's a link to the 11gR1 documentation on the subject: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

1. Unique
2. cluster
3. non-cluster
4. column store
5. Index with included column
6. index on computed column
7. filtered
8. spatial
9. xml
10. full text