Lesson 4: Understanding Indexes in SQL

Executive Summary

Let’s explore SQL Indexes, their importance in database systems, and how you can improve query performance by utilizing them. Just like a book, indexes allow you to easily navigate and improve the data retrieval process. They do have a few trade-offs which will be discussed. There’ll be a few examples of creating and using indexes as well as how to identify when an index may be useful.

Building Example DB:

-- Create User Table
CREATE TABLE Users 
(UserID INT, UserName NVARCHAR(250), UserEmail NVARCHAR(250), UserPhone NVARCHAR(15), UserCountry NVARCHAR(250))
--Add Values to User
INSERT INTO Users (UserID, UserName, UserEmail, UserPhone, UserCountry)
VALUES 
    (1, 'John Doe', 'john@example.com', '1234567890', 'USA'),
    (2, 'Jane Smith', 'jane@example.com', '0987654321', 'Canada'),
    (3, 'Bill Gates', 'bill@example.com', '2345678901', 'USA'),
    (4, 'Elon Musk', 'elon@example.com', '3456789012', 'South Africa'),
    (5, 'Satya Nadella', 'satya@example.com', '4567890123', 'India'),
    (6, 'Sundar Pichai', 'sundar@example.com', '5678901234', 'USA')

Indexes in SQL:

Indexes are utilized to quickly locate data without searching every row in a database. Please see the below example SQL to create an index on UserEmail.

-- Create an index on the 'UserEmail' column of the 'Users' table
CREATE INDEX idx_UserEmail ON Users(UserEmail)

Types of Indexes:

1. Clustered Indexes
   • A clustered index determines the physical order of data in a table. As rows are stored in the same order as the index, a table can only have one clustered index. Usually, the clustered index is created the Primary Key field of the table.
   • Example Code:
     • -- Creating a Clustered Index
CREATE CLUSTERED INDEX idx_UserID ON Users(UserID)
2. Non-Clustered Indexes
   • Non-Clustered Indexes do not sort the physical data inside a table. Instead, a separate object within the table is created, which contains the index key values and a pointer to the data row contained the key values. A table can have multiple non-clustered indexes.
   • Example Code:
     • -- Creating a Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_UserEmail ON Users(UserEmail)
3. Unique Indexes
   • Unique Indexes ensure the index key contains no duplicates and each row is somehow unique.
   • Example Code:
     • -- Creating a Unique Index
CREATE UNIQUE INDEX idx_UserEmail ON Users(UserEmail)
4. Composite Indexes
   • Composite Indexes, like composite Keys, are an index on two or more columns of a table. The order of columns can significantly impact the effectiveness of the index.
     • Example Code:
       • -- Creating a Composite Index
CREATE INDEX idx_CountryEmail ON Users(UserCountry, UserEmail)

Indexes Tradeoffs

• Space Requirements:
  • As you can see from the above, there can be differing levels of storage required for different indexes. As an index essentially creates a copy of the data, more indexes will require more space.
• Write Performance
  • Indexes can speed up read operations, but they can slow write operations. Any time data is altered, all indexes must be updated.
• Maintenance Overhead
  • Indexes require maintenance which may add overhead. Rebuilding or reorganizing indexes may be required and can take considerable time for larger tables.
• Index Selection
  • Choosing which columns to index may be a difficult decision. Over-indexing may be as harmful as under-indexing.

Conclusions:

Using indexes is a balancing act; Too many is just as harmful as too few. Always remember your main goal, speeding up data writing and data reading. Understanding your data and how your users query that data will be key to speeding up queries.