Types of Database Indexes

[beennn]

Types of Indexes:

• Full - examines all of the words in every stored document as it tries to match search criteria (text specified by a user).
• Partial - also known as filtered index is an index which has some condition applied to it so that it includes a subset of rows in the table.
• Partial-Blocked - ?
• Multi - ?

Does anyone know any learning resources where I can read up on partial-blocked and multi indexes? I'm unable to find anything under these terms, perhaps they are known as something else? Also, how do these compare to types of index found here: http://en.wikipedia.org/wiki/Database_index#Types_of_indexes. I assume they are the same just named differently since the definitions are similar, though I'm not sure. If someone could help clarify some of this for me, I'd be very grateful.

 

Any help would be much appreciated.

[justsomeguy]

A multi index might refer to this, an index that covers multiple columns:

 

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

 

I'm not familiar with a partial blocked index.

[beennn]

Thanks for this, looks like it could be - definitely a big help. I believe that method specifically only applies to MySQL databases though; not saying you can't achieve the same thing with other dbms's.

 

I've managed to find what I was looking for, and they do compare to those seen on the wiki, just named differently.

 

I'll post it here since I was unable to find it anywhere else.

• Full (Dense) Index
• Partial (Sparse) Index
• Partial Block Index
• Multi-level Index

Partial Block:

• A partial index can be used to index blocks of data efficiently.
• The partial index contains the key value for the last record in each block.
• As a block is the smallest unit of data that may be read from the disc, the index will always read the minimum number of blocks.

Multi Level:

• When the number of indexed values is large, the index will not fit in one block. Therefore, the contents of the index must be placed in two or more blocks
• As the number of blocks required to store the index grows, so searching the index becomes a major problem. In the same way that a data file takes a long time to search, a large index (occupying many blocks) will also take a long time to search. The solution to this problem is to create an index of an index. That is, the single index is split into a number of blocks and a new index is created that indexes each block. For example, to index a set of names we might split the name index between two blocks. All names beginning with ‘M’ or less are placed in block 1 and all names beginning with letters greater than ‘M’ are placed in block 2. A third block is created points to blocks 1 and 2.
• The indexed sequential file is a sequential file with a corresponding index.

Edited August 6, 2013 by beennn