MySQL Index

• The index of table functions as the index of a book
• Data is taken from a column of the table and is stored in a certain order in a distinct place called an index.
• Your datasets will typically contain 100,000+ or even 1,000,000+ records the larger a database is, the slower the process of finding the record or records you need.
• We can use an index that will increase the speed of searches related to a table
• Apply the index on large datasets don't apply the index on small datasets otherwise the costs of having an index might be higher than the benefits

Syntax to create an Index
CREATE INDEX index_name
ON table_name (column_1, column_2, …);

Note: The parentheses serve us to indicate the column names on which our search will be based.
These must be fields from your data table you will search frequently.
For example
CREATE INDEX i_hire_date ON employees(hire_date);

Composite Indexes
Applied to multiple columns, not just a single one carefully pick the columns that would optimize your search.
Primary and unique keys are MySQL indexes they represent columns on which a person would typically base their search.