SQL Indexes Explained
- You can create SQL indexes from single or multiple columns.
- A SQL index is like the index of a book. It speeds up the retrieval of a record. The relational database management system (RDBMS) can retrieve a record with the index key instead of having to perform a table scan.
- MySQL automatically creates indexes for primary and foreign keys significantly speeding up join performance.
- You should only create indexes on columns used in a join or search because the RDMS must update an index every time you execute an INSERT, UPDATE, or DELETE.
When to Create an Index
- When a column is used frequently in a search or a join.
- When a column contains a large number of distinct values.
- When the column is updated infrequently