In Part 3, we talked about when indexes are created automatically and when we should create them.
Most databases create indexes automatically for primary keys and UNIQUE columns. Except for MySQL, databases typically don't create indexes on foreign keys.
Instead of using a UNIQUE constraint with an ordinary index, you can create a unique index:
CREATE UNIQUE INDEX name_unique_index ON subway_station(name);
However, this syntax is rarely used.
Because indexes take up space and slow down insert, update, and delete times, we shouldn't create many of them when we're creating database tables. Instead, we should add indexes to specific columns when our SQL queries slow down.
Modifying a lot of data in an indexed table can be very slow. It may be a good idea to first remove the indexes, perform the modifications, and then create the indexes again.
If you want to delete all data from a table, avoid the DELETE FROM expression, which deletes one row at a time. Instead, try this much faster way:
TRUNCATE TABLE players;
Note that TRUNCATE TABLE won't work if your table contains any foreign keys.
Let's do another exercise before we move on.
You're given the same 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 column code should be unique. Create a unique index named unique_index_code on this column.
