Database Reference
In-Depth Information
Indexing tuples
In the previous section, we observed that it would not make much sense for a user to have a
graduation year without also naming an educational institution; this provided a rather ab-
stract motivation for grouping those two pieces of information together in a single column.
A more practical motivation is the fact that, like any other column, tuples can be indexed.
Let's put an index on our education column; the syntax is no different from creating an in-
dex on any other column:
CREATE INDEX ON "users" ("education");
Armed with this index, we can now perform efficient lookup of user records based on their
educational institution and year of graduation:
SELECT "username", "education" FROM users
WHERE "education" = ('Big Data University', 2003);
As we would hope, the query produces
alice
's record:
The ability to index tuples provides an important workaround to a constraint we discussed
in the section
Limitations of secondary indexes
of
Chapter 5
,
Establishing Relationships
.
As you learned in that chapter, a secondary index can only be applied to a single column.
If, for instance, we had separate
school_name
and
graduation_year
columns, it
would be impossible to create an index that allowed us to efficiently look up records with a
given combination of values in these two columns. Using a tuple, we can place both values
in a single column and index that column, giving us much the same effect as a multicolumn
index.
