This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:concepts:home [2012/01/20 18:37] doc |
admin:indexing:concepts:home [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 31: | Line 31: | ||
| This is where indexing is useful, but indexing comes in many different flavors. When we go to the hardware store, we're smart enough to go to the washer aisle, 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 is still too many trips. It may surprise you, but many databases use this approach. They pick a primary index and retrieve rows based on that one index, but all other criteria is processed by the CPU after it is retrieved from the disk. We would not tolerate this approach at the hardware store, and we don’t have to tolerate it with databases either. | This is where indexing is useful, but indexing comes in many different flavors. When we go to the hardware store, we're smart enough to go to the washer aisle, 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 is still too many trips. It may surprise you, but many databases use this approach. They pick a primary index and retrieve rows based on that one index, but all other criteria is processed by the CPU after it is retrieved from the disk. We would not tolerate this approach at the hardware store, and we don’t have to tolerate it with databases either. | ||
| - | 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, we can do the same thing using 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. If there are twenty pieces of criteria across five tables, then use twenty indexes across five tables. Retrieve only the rows from the database that match all the criteria. This greatly reduces the I/O to the disk drives. | + | 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, we can do the same thing using indexes. If there are six elements of criteria (such as a “1/2-inch rubber washer for a Delta kitchen faucet”), then use the intersection of six indexes. If there are twenty pieces of criteria across five tables, then use twenty indexes across five tables. Retrieve only the rows from the database that match all the criteria. This greatly reduces the I/O to the disk drives. |
| - | Companies who use Omnidex swear by the performance. It may look like magic, but it is really just intelligent use of indexes. Companies who have grown accustomed to making endless trips to the disk drives think it is a miracle when they reduce their I/O and queries become lightening fast. 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 needed a washer. | + | Companies who use Omnidex swear by the performance. It may look like magic, but it is really just intelligent use of indexes. Companies who have grown accustomed to making endless trips to the disk drives think it is a miracle when they reduce their I/O and queries become lightening fast. 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 bring home a broom when we needed 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. There are indexing strategies for criteria, for table joins, for aggregations and for ordering. Ultimately, that is the goal of the Omnidex Administrator … to use indexing as fully as possible to make each query efficient and fast. | 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. There are indexing strategies for criteria, for table joins, for aggregations and for ordering. Ultimately, that is the goal of the Omnidex Administrator … to use indexing as fully as possible to make each query efficient and fast. | ||