In Part 4, we discussed indexes on expressions, partial indexes, and clustered indexes.
We explained that databases can't use indexes if the indexed columns are "hidden" inside function calls (such as LOWER(last_name)). The same is true if the indexed columns are used in expressions like first_name || ' ' || last_name. In such cases, we have two options: we can either rewrite the query so that indexed columns are available, or we can index entire expressions.
Rewriting a query is not always possible. We may sometimes be forced to index an expression, like this:
CREATE INDEX company_insensitive ON dev_award (LOWER(company));
If there's no function call in the expression, you need an additional pair of parentheses:
CREATE INDEX full_name ON dev_award ((first_name || ' ' || last_name));
Some databases support partial indexes. In such cases, only a subset of rows is indexed. To create a partial index, use a WHERE clause:
CREATE INDEX lname_sql ON dev_award (last_name) WHERE category = 'sql';
Finally, we mentioned that some databases use clustered indexes. A clustered index influences how rows are physically written to the hard drive. However, there's no standardized syntax for clustered indexes; check your database's documentation for more details.
Ready for the next exercise?
Given the smartphone table:
CREATE TABLE smartphone ( id integer PRIMARY KEY, code varchar(32), manufacturer varchar(32), model varchar(32), price_usd decimal(10, 2), memory_gb integer, screen varchar(32), main_camera_mpx integer, release_date date );
Your task is to index an expression which is often used to search for phones:
manufacturer || ' ' || model
Name the index manu_model_concat_index.
