Hi everyone! One of the most important skills to develop as you get comfortable writing SQL queries is writing performant queries. This is particularly important if querying a large dataset. Poorly optimised queries can slow down an application’s performance, leading to unnecessary resource consumption and, in extreme cases, even causing system failures. For this week’s blog, I’ll be focusing on Indexing and how it can make your queries faster and more efficient.
Ways to Optimize SQL Queries for Performance using Indexing
Indexing
In SQL, an index is a data structure that speeds up data retrieval from a table, at the cost of additional storage and slightly slower data modification operations (INSERT, UPDATE, DELETE). Think of it like a book’s index. It lets you quickly locate specific information without scanning every page. Without an index, SQL queries must perform a full table scan, checking every row to find matching data. Proper indexing can drastically reduce query time, especially in large tables.
MySQL Index Example
Table: Employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50)
);
Data:
| EmployeeID | Name | Department |
|---|---|---|
| 1 | Alice | Sales |
| 2 | Bob | Marketing |
| 3 | Carol | IT |
| 4 | Dave | Sales |
| 5 | Eve | IT |
Scenario: Find the employee with EmployeeID = 4
Without Index:
SELECT * FROM Employees WHERE EmployeeID = 4;
- MySQL will do a full table scan, checking each row one by one:
1 -> 2 -> 3 -> 4 ✅ found - Time grows linearly with table size.
With Index on EmployeeID (PRIMARY KEY automatically creates one):
ALTER TABLE Employees ADD INDEX idx_employee_id(EmployeeID);
SELECT * FROM Employees WHERE EmployeeID = 4;
- MySQL uses the index (like a mini lookup table) to jump directly to
EmployeeID = 4. - Much faster, especially in large tables.
Some ways Indexes help
Some Ways Indexes Help
1. Faster Query Execution
Indexes allow MySQL to locate the rows you need without scanning the entire table. As I mentioned earlier, it’s like using a book’s index to jump directly to a topic instead of reading every page. For example, if you search for EmployeeID = 4, in a table with thousands of employees, an index can find it almost instantly.
2. Improved Sorting
Indexes can also help when you use ORDER BY in queries. Instead of sorting all rows manually, SQL can leverage the index to quickly retrieve data in the desired order. For instance:
SELECT * FROM Employees ORDER BY EmployeeID;
With an index on EmployeeID, MySQL can return sorted results efficiently.
3. Efficient Joins
When joining tables, indexes allow MySQL to quickly match rows between tables. Suppose you join Employees and Departments on DepartmentID. If both columns are indexed, MySQL doesn’t need to check every combination. It uses the indexes to match rows directly.
4. Enhanced Filtering
Indexes make WHERE clauses faster by narrowing down the rows MySQL needs to examine. For example:
SELECT * FROM Employees WHERE Department = 'IT';
An index on Department will let MySQL instantly find only the relevant employees, instead of scanning the full table.
Type of indexes
There are several types of indexes in SQL, each serving different purposes. The most common ones are:
(1) Single-Column Indexes: These indexes are created on a single column. When querying filters, sorting, or joins using that column, the database can use the index to retrieve data more quickly.
Example: Indexing an employeeID column in the employees table
(2) Composite Indexes (Multi-Column Indexes): A composite index is an index that is created on multiple columns. It is particularly useful when queries involve multiple columns in the WHERE clause, ORDER BY, or JOIN conditions
Composite Index Example: Employees Table
Table Structure:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50),
Role VARCHAR(50)
);
Sample Data:
| EmployeeID | Name | Department | Role |
|---|---|---|---|
| 1 | Alice | Sales | Manager |
| 2 | Bob | Marketing | Analyst |
| 3 | Carol | IT | Engineer |
| 4 | Dave | Sales | Executive |
| 5 | Eve | IT | Manager |
Scenario: Query Employees by Department and Role
SELECT *
FROM Employees
WHERE Department = 'IT' AND Role = 'Manager';
- Without Composite Index: MySQL scans the entire table, checking both
DepartmentandRolefor each row. - With Composite Index: Create a multi-column index on
(Department, Role):
ALTER TABLE Employees ADD INDEX idx_dept_role (Department, Role);
Now MySQL can directly locate rows where Department = 'IT' AND Role = 'Manager'.
Result:
| EmployeeID | Name | Department | Role |
|---|---|---|---|
| 5 | Eve | IT | Manager |
Composite indexes are highly beneficial for queries that involve conditions on multiple columns.
(3) Unique Index: A unique index ensures that all values in the indexed column(s) are distinct. This prevents duplicate values in a column that must be unique, like an email or employee ID.
Example Table: Employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100)
);
Add a unique index on Email:
ALTER TABLE Employees ADD UNIQUE INDEX idx_email_unique (Email);
Scenario:
You want to ensure that no two employees can have the same email.
Query Example:
INSERT INTO Employees (EmployeeID, Name, Email)
VALUES (6, 'Frank', 'eve@example.com'); -- Will fail if Eve already exists
Benefit: Prevents duplicates and enforces data integrity while speeding up searches on the column.
(4) Full Text index: A full-text index allows fast text searching within large text columns, such as searching for words in articles, descriptions, or notes.
Example Table: Articles
CREATE TABLE Articles (
ArticleID INT PRIMARY KEY,
Title VARCHAR(255),
Content TEXT
);
Add a full-text index on Content:
ALTER TABLE Articles ADD FULLTEXT INDEX idx_content_fulltext (Content);
Query Example:
SELECT *
FROM Articles
WHERE MATCH(Content) AGAINST('database performance');
Benefit:
Full-text indexes let you efficiently search for words or phrases inside long text fields, which would otherwise require slow full table scans.
(5) Spatial Index: A spatial index is used for geospatial data like coordinates, polygons, and maps. It allows fast queries based on location, distance, or geometric relationships.
Example Table: Stores
CREATE TABLE Stores (
StoreID INT PRIMARY KEY,
Name VARCHAR(50),
Location POINT,
SPATIAL INDEX idx_location (Location)
);
Query Example: Find stores near a specific point
SELECT Name
FROM Stores
WHERE ST_Distance_Sphere(Location, POINT(43.6532, -79.3832)) < 5000; -- within 5km
Benefit:
Spatial indexes allow efficient geospatial queries, which would otherwise require scanning every row and calculating distances manually.
When to Use Indexes
- Frequently Queries Column
- Columns with High Selectivity – high selectivity means columns with a lot of unique values, for example, ‘Emails’
- Primary and Foreign Keys – always index the primary key and the foreign key since they’re often used to join tables.
When Not to Use Indexes
While indexes can greatly improve query performance, they do come with trade-offs. Indexes consume additional disk space and can slow down data modification operations. Such as INSERT, UPDATE, and DELETE because the index must be updated whenever the data in the indexed columns changes. As such, avoid creating indexes on columns that are:
- Frequently Updated
- Low Selectivity
- Small Tables
Thank you for reading and subscribing to get more tips on SQL optimisation.
