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.
Lesson Summary
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.
