Index Intersection involves scanning multiple indexes for records matching criteria such as:
field1 = 'expression' AND field2 = 'expression'
As described in the previous section, Rushmore uses the indexes on both fields to find matching records for each part of the criteria. The results from each of the indexes are intersected to find the records that match both criteria.
For example, suppose you have a table that contains demographic information about all the people in New York City. The table contains information regarding each person’s age, height, weight, hair color, and eye color. Suppose you write the following query to find all the people with blonde hair and blue eyes:
SELECT * FROM People WHERE hair_color = 'blonde' AND eye_color = 'blue'
Assuming indexes have been created on the hair_color and eye_color fields, this query would be solved using Rushmore Index Intersection. The index on hair_color is used to find all the blonde-haired people, and then the index on eye_color is used to find all the blue-eyed people. The results of each index search are then intersected to find all the people with both characteristics.
Index Union involves scanning multiple indexes for records that match criteria such as:
field1 = 'expression' OR field2 = 'expression'
Again, Rushmore uses the indexes on both fields to find the matching records. The resulting records from each of the indexes are then combined to find the records that match either criterion.
For example, again suppose you have a table that contains demographic information about all the people in New York City. Suppose you write a query to find all the people with black hair or green eyes. The criteria would look like:
SELECT * FROM People WHERE hair_color = 'black' OR eye_color = 'green'
Using Rushmore Index Union, the index on hair_color is used to find all the black-haired people, and the index on eye_color is used to find all the green-eyed people. The result of each index search is then combined to find all the people with either characteristic.