An Absolute Beginner's Introduction to Database Indexes | Kyle's Cousin

An integer datatype is used here, because it’s the fastest and most efficient datatype to use for primary keys.CREATE INDEX idx_last_name ON customer (last_name);
CREATE INDEX idx_fullname ON customer (first_name, last_name);

MySQL organizes your indexed data in a tree structure that make look-ups faster. It simply follows the path in a tree to find the records you need, which is considerably faster than a full table scan.
If you use the InnoDB engine, you can add foreign keys to the customer and product table. MyISAM does not support this at the time of writin

SELECT CONCAT(c.first_name, ' ', c.last_name) AS fullname, r.review AS review FROM review r

LEFT JOIN customer c ON r.customer_id = c.id

This query joins 2 different tables, without any indexes. What’s important here is the field is are used for joining these tables: “customer_id”. We need a way so MySQL can easily join these tables without having to loop through all records.

1	CREATE INDEX idx_customer_review ON review (customer_id);

This will greatly increase the execution speed of the previous select query on large data sets.
You can do the same thing for “product_id” if you need to do joins with the product table. In almost all circumstances, indexes are beneficial on fields that are used to join tables.

any filter you perform on a non-indexed field will require a full scan of the table. So in principle, every field that you need to perform queries on should be indexed. Unfortunately, it’s not that simple. An index may improve your selection queries, but it will slow down insert and update queries. Pretty normal, considering that each time an insert, update or delete statement is executed, the index needs to be altered as well in order to keep it functional.

Another disadvantage is disk space… The more indexes you create, the more data has to be stored. Luckily, the space an index needs is almost always lower than the space that the table needs

UNIQUE INDEX: this index is used on a column to define that it can have no duplicate values. Each row must have a different value for this column. In fact, primary indexes are also unique indexes, but a unique index is not necessarily a primary index.

1	CREATE UNIQUE INDEX idx_product_name ON product (name);

FULLTEXT INDEX: this index type is mainly used on fields which contain large amounts of text data (CHAR, VARCHAR and TEXT). 
CREATE FULLTEXT INDEX idx_product_description ON product (description);
Read full article from An Absolute Beginner’s Introduction to Database Indexes | Kyle's Cousin