Differences

This shows you the differences between two versions of the page.

--- admin:indexing:concepts:basics [2011/01/14 19:23]
els
+++ — (current)
@@ Line 1: / Line 1: @@
-{{page>:top_add&nofooter&noeditbtn}}
-====== Administration: Indexing Strategies ======
-===== Indexing Concepts =====
-[[admin:indexing:concepts:home|Overview]] |
-**[[admin:indexing:concepts:basics|Basics]]** |
-[[admin:indexing:concepts:criteria|Criteria]] |
-[[admin:indexing:concepts:joins|Joins]] |
-[[admin:indexing:concepts:aggregations|Aggregations]] |
-[[admin:indexing:concepts:ordering|Ordering]] |
-[[admin:indexing:concepts:retrieval|Retrievals]] |
-[[admin:indexing:concepts:alternatives|Alternatives]]
-----
-==== Indexing Basics ====
-Someone once told a story:
-==  ==
-One day, a homeowner noticed that his kitchen sink was leaking.  He fiddled with the faucet and concluded that the washer needed replacing.  He headed down to the hardware store, purchased the replacement washer and returned home to install it.  As soon as he tried to put the new washer in, he realized that it was the wrong size, so he headed back to the hardware store.  After getting the correct washer, he returned, only to find that he once again had purchased the wrong washer.  In his frustration, he removed the whole faucet and brought it into the hardware store.  Once there, he was able to match it up correctly.  He returned home, reinstalled the faucet, and finally was able to call the job finished.
-====  ====
-This story tells us something about indexing.  Hardware stores are like big databases.  They have all the important things that we want, and they are usually organized well enough that we can find what we want.  The most expensive thing, though, is the trip to the hardware store in the car.  We want to make sure that we get everything in one trip.
-It would certainly be painful if we had to make a trip to the hardware to get a 1/2" rubber washer for a Delta kitchen faucet, but we didn't have the benefit of looking at the aisle markers or asking for help.  Imagine if we have to put a blindfold on, reach for the first item in the first aisle, and return home to see if we got the right item.  Once home, we compare out item to the failed faucet washer, and we realize that the broom that we blindly retrieved was not the washer that we needed.  So we head back to the hardware store, get the next item, bring it back, and find it is another identical broom.  We repeat this process for all the brooms, mops, vacuum cleaners, tools, furnace filters and doorbells until we finally get to the washers.  Then we make repeated trips exhaustively checking the washers until we find what we are looking for.  This insane and amusing metaphor shows the cost of a full table scan for a database.  It evaluates every single row, transports the row from the disk drive to the CPU where it can do the comparison, and repeats this process ad nauseum.  In the end, it was the trips to the disk drive that hurt performance, just like the endless trips to the hardware store.  With database applications, you've got to minimize the I/O to the disk drive.  That is the overwhelming cost.
-This is where indexing comes in.  But indexing comes in many different flavors.  When we go to the hardware store, we're smart enough to go to the aisle of washers, so we thankfully avoid the brooms, mops and vacuum cleaners, but with only one piece of criteria, we still have to bring each and every washer back to the house.  That's still way to many trips.  It may surprise you, but many databases use this approach.  They pick a primary index and retrieve rows based on that index, but all the other criteria is processed by the CPU after it is retrieved off of disk.  We would not tolerate this at the hardware store, and there certainly has to be a better way with databases as well.
-When the homeowner took the faucet to the store and matched up the washer there, he applied all criteria at the same time.  With databases, it is important to do the same thing with indexes.  If there are six elements of criteria (such as a 1/2" rubber washer for a Delta kitchen faucet), then use the intersection of six indexes.  Only retrieve the rows from the database that match all six pieces of criteria.  This greatly reduces the I/O to the disk drives.
-Companies who use Omnidex swear by the performance.  It is as though it is magic, but it is really just careful use of indexes.  Companies who were used to making endless trips to the hardware store (aka the disk drive) thing it is a miracle when they start making only a fraction of the trips.  They realize that it never really helped them to retrieve rows from the disk that they didn't need, just like it didn't help to retrieve a broom when we need to replace a washer.
-This somewhat silly story is actually quite useful as a foundation for understanding Omnidex performance.  Throughout these sections, you will see an emphasis on insuring that there is indexing to support each step of the query.  Ultimately, that is the goal of the Omnidex administrator ... to use indexing as fully as possible to minimize unnecessary trips to the disk drive.
-When we go to the hardware store to find a washer, we have the benefit of being able to go to the aisle containing washers.  How did we know do that?  Well, obviously, we looked at the signs or asked the clerk where the washer aisle was.  This is like using a primary index to narrow a database.  For example, a query may begin with a search against the STATE column.  If we can reduce millions of rows down to just those in a particular STATE, we've greatly reduced the amount of data to examine.
-But what if there is more criteria than just STATE.  What if there is also a CITY.  Once we get to the washer aisle in the hardware store, we know to look for faucet washers.  That is
-=====  =====
-**[[admin:indexing:concepts:criteria|Next]]**
-====== Additional Resources ======
-See also:
-{{page>:admin:indexing:see_also&nofooter&noeditbtn}}
-{{page>:bottom_add&nofooter&noeditbtn}}

Back to top

admin/indexing/concepts/basics.1295033017.txt.gz · Last modified: 2012/10/26 14:52 (external edit)

Sitemap Recent Changes