Tips For Indexing Your Database

If you are using a database or a series of connected databases to store information, you are going to have something that is called an index to look up each item. For example, if you have a database that is full of different types of food, you might first have food categories to help narrow down your search if you are searching for a particular dish. You might have categories such as "vegetables," "chicken," and "pork." Then, each of those keywords will lead you to another table that is full of even narrower search terms. If you search the main food categories table and then go to chicken, you might index a table that contains the entries "grilled chicken" and "fried chicken." From there, you can index a table that contains "fried chicken sandwich" and "fried chicken tenders."

You need to be sure that you organize your databases in a way that makes sense and results in the answer to the query being returned within a short period of time. Here are some tips for optimizing your database setup so that queries get answers back quickly.

1. Keep Your Indexing Based on Integers, Not Characters

It is far easier for a computer to recognize and compare different integers, as opposed to different characters or different strings of characters. If you are starting at the main food categories table and need to find "pork," it is going to be easier for the computer if you keep them in alphabetical order and then use integer keys that relate to the alphabet order to do look-ups. This will make any searches that you make much faster.

2. Keep the Number of Sub-Tables Small

It takes time for a computer to index sub-tables. As a result, you don't want to go too deep from table to table. Try to organize each sub-table so that you limit the number of tables that you actually have to index in order to get to the answer to a query. In the example of the food query above, you only needed to index three times in order to get to "fried chicken sandwich," if that were the dish that you were looking for.

If you are going to be doing a read-only table, meaning that you aren't going to be editing any entries in the table or adding to the table, then it is acceptable to require a larger amount of indexing since reads tend to take less time than adds or edits. 

For more information, talk to your data manager. He or she will have other tips on how to keep your database setup optimized.

For data center management, contact a company such as Cologix.