How to create a multi-column index in SQL:
We could also create an index on two of the columns in the Employee table , as shown
in this SQL:
CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)
What is a good analogy for a database index?
A very good analogy is to think of a database index as an index in a book. If you have a
book about dogs and you are looking for the section on Golden Retrievers, then why
would you flip through the entire book – which is the equivalent of a full table scan in
database terminology – when you can just go to the index at the back of the book,
which will tell you the exact pages where you can find information on Golden
Retrievers. Similarly, as a book index contains a page number, a database index
contains a pointer to the row containing the value that you are searching for in your
SQL.
What is the cost of having a database index?
So, what are some of the disadvantages of having a database index? Well, for one
thing it takes up space – and the larger your table, the larger your index. Another
performance hit with indexes is the fact that whenever you add, delete, or update
rows in the corresponding table, the same operations will have to be done to your
index. Remember that an index needs to contain the same up to the minute data as
whatever is in the table column(s) that the index covers.
As a general rule, an index should only be created on a table if the data in the indexed
column will be queried frequently.