Instruction
Perfect! Let's look at another example. The foundation has created a website where people can search for awarded developers by first and last name. The names are provided together in a single search box. This means we often need to run queries like this one:
SELECT * FROM dev_award WHERE (first_name || ' ' || last_name) = 'John Smith';
Even though there is no function call in the WHERE clause, we're actually filtering rows by an expression:
(first_name || ' ' || last_name)
Again, we can't simply create indexes on the columns first_name and last_name to make this kind of search quicker. Instead, we need to create an index on the entire expression:
CREATE INDEX full_name ON dev_award ((first_name || ' ' || last_name));
Note that there are double parentheses around first_name || ' ' || last_name. The additional pair of parentheses is required when the expression is not a simple function call.
Exercise
We're again given the house table:
CREATE TABLE house ( id integer PRIMARY KEY, state char(2), zip_code char(5), price decimal(10, 2), bedrooms integer, built_year integer );
The agency also frequently looks for available apartments using their special house code. The house code consists of the state, a hyphen, and the number of bedrooms. For example, "NY-3" is the code for an apartment in New York with 3 bedrooms.
Create an index named agency_code on the agency's special house code, as explained above.
Stuck? Here's a hint!
Use:
((state || '-' || bedrooms))
