Indexes

When you query the customer table, the server will need to inspect every row of the table to answer the query. This type of access is known as a table scan.

An index is simply a mechanism that lists words and phrases in alphabetical order for finding a specific item within a resource (for example at the end of a book).

The role of indexes is to facilitate the retrieval of a subset of a table’s rows and columns without the need to inspect every row in the table.

Index Creation

alter table customer
add index idx_email (email);
# a B-tree index

alter table customer
drop index idx_email;

This will add an index to speed up specifying update or delete addresses.

<aside> 💡 MySQL treats indexes as an optional table component. Oracle and SQL Server treat it as an independent schema object.

</aside>

When the table was created, the MySQL server automatically generated an index on the primary key column.

create index idx_date_amount
on payment (payment_date, amount);
# alternative

Unique indexes

alter table customer
add unique index idx_email (email);

# Error Code: 1062. Duplicate entry '[email protected]' 
# for key 'customer.idx_email'

• You should not build unique indexes for primary key since it is redundant.
• You can create multiple columns with unique feature.

Multicolumn indexes

alter table customer
add index idx_full_name (last_name, first_name);

• You can build multiple indexes using the same set of columns but in a different order, careful for performance time.
• When building multiple-column indexes, you should think carefully about which column to list first to help make the index as useful as possible.

Types of Indexes