by Red Giant

Created on: June 11, 2009

Indexes improve performance! I could stop there, but the article criteria requires me to add another 397 words.

Imagine going to a super Walmart or Target in search of a bottle of shampoo. You could walk to the front right corner of the store and begin walking up and down the isles until either a) your find the shampoo you need, or b) you reach the back left wall of the store. Think of this scenario as a data search for one "item" in a 100,000,000 row "products" table. You say, "Oh my! I just remembered that I need cat food." You now get to repeat the same arduous process, as just described, in the hope of finding food for your cat. I do hope you wore comfortable shoes!

While store executives would love for you to shop accordingly, hoping for additional sales from impulse purchases, they know you may never return to the store if you had to repeatedly endure the lengthy walk and overly time consuming shopping experience. Therefore, stores are organized - we could say indexed - to make your shopping experience more efficient. So, with "indexing" in place, you now start searching for your shampoo by looking for a sign saying "Bath and Beauty" hanging from the ceiling. After a short but determined march to that area, you begin looking down the aisles for a sign declaring "Shampoo". Once you transport yourself to the shampoo section of the isle you "sort" methodically through the shampoo selections to find the perfect shampoo. Wow! That process seems to be much quicker. Its the same performance improvement concept that facilitates index usage on database tables.

Since we now understand that the indexed process is much quicker, let's go a little deeper into the search criteria. For indexes to be effective, they must be designed with the search criteria in mind. Let us continue our example. You have to know what data your are looking for - "select item" - from where the data is stored - "from products" - and what type of item you need - "where item = shampoo". The index would need to be built on the "item" data. So, if the "products" table has 400 shampoo selections, minus some technical details, the search would only look at 400 of the 100,000,000 possible selections. That makes much more sense and is significantly faster! Your search criteria could be refined to include - "and manufacturer = Dove" - to further reduce the selection down to let's say twenty Dove shampoo items. If the index had been built on the "price" data, your search would have encompassed the 100,000,000 items since your search attribute did not involve price. Now, if you just thought to yourself, "What if I want to pick the least expensive shampoo", then yes, you could build an index based on the pricing data.

In summary, indexes improve performance!

Learn more about this author, Red Giant.
Click here to send this author comments or questions.