You can create SQL indexes from single or multiple columns.
A SQL index is like the index of a book. It speeds up 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 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
Clustered vs Non-clustered indexes
Clustered index: The values in the column indexed are physically stored in alphabetical
or numeric order.
You can only have one clustered index per table.
If you assign a primary key, the system automatically creates a clustered index on that column.
If no primary key is defined on a table, the first column that has an index defined for it
becomes the clustered index.
Non-clustered index: Column values are not in alphabetical or numeric order
You can add as many non-clustered indexes to a table as you want.
You should only create additional non-clustered indexes on a table if you need to search or
perform a join on that column. When you create a foreign key column, a
nonclustered index is automatically created for that column.
How to Manually Create an Index in an ERD
Right-click on the table and select Edit
Click on the Indexes tab
Type the name of the index in the Index Name field
Under Type select INDEX
Click on the column(s) that you want to index.
Tab to a new line
How to Manually Add an Index to an Existing Table
Right-click on the table
Select Alter Table
Click on the Indexes tab
Type the name of the index in the Index Name field
