Michelle has been an academic librarian for over twenty years. She has a Master’s Degree in Library Science from the University of Wisconsin-Madison.
What a Database Index Is
Have you searched for something on the computer and wondered how the information seemed to magically appear? How did the computer know exactly what you were searching for and retrieve it for you? Was it the elves who live in the computer? No, most likely it was a database index that located the specific information.
A database is an organized collection of information. To save you time, a database index helps you retrieve specific data. A database index is a lot like an old-fashioned recipe box with dividers. The recipe box directs you to different categories such as soups, salads, and vegetables. But a database index takes searching a step further. You can search for your favorite salad recipe, not just browse through broad categories.
Another example of a database index is an online catalog at a library. In this case, if we're searching for One Flew Over the Cuckoo's Nest, you can search by author, title, or by subject heading.
Okay, let's now explore more about database indexes, including the advantages and disadvantages of different kinds of indexing tools.
Spreadsheet v. Database Index
Let's talk about the differences between a spreadsheet and a database index. Often, they may have a similar look with tables, rows, and columns. The purpose of a spreadsheet is to tabulate numerical data. They are used for smaller pieces of information. Microsoft Excel is a great example. In contrast, database indexes are used for organizing and retrieving vast amounts of data. A great example is Microsoft Access.
Full-Text Search Engine Comparison
In addition to spreadsheets, full-text search engines can be similar to databases as well. However, let's point out some of the differences between the two programs. Full-text search engines differ from database indexes in many ways. For instance, every word is included in the full-text index, not just certain fields. When every word is counted, it can lead to very large search retrievals.
In a search engine, spiders gather the information. When you complete a search, you are taken to the metadata for the specific page. Metadata is data about data. Manipulating the data by importing or exporting it into another file is not usually an option. Google is the best known full-text search engine.
In a database index, only certain fields and rows are searched, not the entire text. The retrieval can be much smaller than with a full-text search engine. The audience can be much more specialized as well.
Some database indexes have their own vocabulary, sometimes called a controlled vocabulary, that allows the searcher to target their search results. The ERIC database, which is a collection of education-related information, is an example of an index with a controlled vocabulary.
Another advantage is that you can manipulate the data by exporting or importing it. Also, you are taken to the actual data, not the metadata. Ancestry is another example of a database index.
Database Index Types
Database indexes can be pictured as a tree with lots of branches. In some cases, the branches might have leaf nodes, which contain the information. Some branches might have a parent and child that shows the continuation of the data structure.
Since information needs are constantly evolving, there is an array of different types of databases indexes. B+ and B- trees are some of the most common types of database structures.
A B+ tree database index is a balanced tree with many branches, and when you complete a search, the entire database index is scanned. If you need a database for complex searches, this would be a great choice.
For more simple queries, a B- tree might work for you. A B- tree database index is a self-balancing tree with only a few branches. Adding or deleting information is easy in this structure. This is a lean and mean type of index.
Yet another type of database index is the R- tree. You have probably used this type of index before without realizing it, especially on a Smartphone. The R- tree database index is used for spatial, or location, data. An example would be searching for the nearest coffee shop within a two-mile radius.
Finally, a hash table is another type of database index. In a hash table database index, the key information is paired and stored for quicker retrieval. The information's not orderly like in other indexes. Also, the results are not sorted. The advantage of a hash table is that it's best for simple search queries.
Categories of Database Indexes
Database indexes can be divided into two main categories: clustered and non-clustered database indexes.
Why is it called a clustered index? Well, the information is physically clustered in the index row. The information lives in the leaf node. A leaf node is independent without a parent of child. A clustered index is known for being efficient and useful for searches that are commonly run.
A non-clustered index does not physically contain the data but, rather, a pointer directs the search to the information. A pointer is like an arrow that relies on memory. An example of a non-clustered index is an index in a book. When you use the index to look at a topic, you're guided to the exact page with the information.
Let's review. A database index is a tool designed to help you save time, whatever your information needs. A database index can be as simple as a back-of-the-book index, as seen in the non-clustered index example. Or the information can be physically contained as in the clustered index. B+ tree database index structures are multifaceted with many branches. The B- tree database index features elegant simplicity with its simple structure. Millions of people use the R- tree database index each day to obtain geographic information. On the other hand, a hash table database index is a quick search retrieval. Whether you have simple or complex needs, there's a database index to suit you.
To unlock this lesson you must be a Study.com Member.
Create your account
Register to view this lesson
Unlock Your Education
See for yourself why 30 million people use Study.com
Become a Study.com member and start learning now.Become a Member
Already a member? Log InBack
Resources created by teachers for teachers
I would definitely recommend Study.com to my colleagues. It’s like a teacher waved a magic wand and did the work for me. I feel like it’s a lifeline.