ChompingTheBit

This week a big topic we talked about was how to make database queries more efficient. One of the ways that we can make databases queries faster is by using indexing, however it is important to understand its limitations.

In a database, an index makes queries to the database faster by making a relationship between a piece of data you’re interested in, and a key to a table that stores that data. This is just like how an index at the back of a book helps us find the page numbers where a certain topic is mentioned. An index gives the computer a quick reference of where to find relevant data, which speeds up queries by avoiding having to search larger sections of a database.

One important thing to keep in mind is that improperly using indexes can cause drawbacks. Because an index makes the computer keep a relation of a column and a key in the database, whenever a table with indexes is written to or updated, all the indexes need to be updated. Because of this it is a good practice to only use indexes for tables/columns that are frequently read from and not frequently written to.

Another thing to avoid is something that might be called a “slow index”. Slow indexes are indexes which unexpectedly slow down the speed of a query instead of making them faster. This is usually caused by a mistake from the user rather than an actual slow index.

When a database uses an index, it traverses the B-tree, follows the leaf node chain, and then returns all relevant data. These last two steps can cause slow queries if the user is asking for require the computer to look at many matching entries. To return to the human comparison, this would be like if the index in your encyclopedia or cookbook has pages of references for any single topic. If you had to read through 1-2 pages of references to page numbers where a topic is mentioned, it might still take you hours to find what you were initially looking for.