Instruction
In Part 2, we learned the basic SQL syntax for working with indexes.
We create single-column indexes like this:
CREATE INDEX points_index ON player (current_points);
We can also create multiple-column indexes in a similar way. Remember, the column order matters!
CREATE INDEX player_multi_country_points_index ON player (country, current_points);
This index will be used in queries if the WHERE clause uses index columns in the same order they are defined in the index:
By default, index rows are sorted in ascending order and NULL values appear last. We can change that behavior by adding ASC, DESC, NULLS FIRST, and NULLS LAST after the column names:
CREATE INDEX player_multi_country_points_index ON player(country DESC, current_points DESC NULLS LAST);
Finally, we showed you how to delete indexes:
DROP INDEX points_index;
All right, how about a few exercises now?
Exercise
An electronics megastore has the following 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 );
The store noticed a performance problem when users look for phones based on the manufacturer and model columns. Create a multiple-column index named index_manufacturer_model on the columns manufacturer and model (in this order).
