What are the indexes, advantages + disadvantages?

An index is a separate structure the database keeps next to a table so it can find rows by a column value without scanning the whole table. Queries that filter, join or sort on an indexed column read far fewer pages, so SELECT, JOIN, WHERE and ORDER BY get faster. A unique index also enforces that no two rows share the same value.

Single-column index

Created on one column. It helps when you often search, filter or sort by that column.

This creates an index named idx_product_id on the product_id column of the Sales table.

Multi-column index

Created on two or more columns. It helps when queries filter or join on those columns together.

With this index the database can filter or join on product_id and quantity together without a full scan.

Advantages

1. Faster SELECT queries
2. Cheaper sorting and filtering
3. Faster joins
4. Fewer full table scans

Disadvantages

1. Slower INSERT, UPDATE and DELETE, because every index on the table has to be updated as well
2. Extra storage
3. Maintenance overhead
4. Too many indexes slow writes down more than they speed reads up