About the Series

SQL optimization is the process of tuning SQL queries to improve server performance. The fundamental idea is to reduce the amount of time it takes for a user to retrieve a response after performing a query, as well as the number of resources required to process one.

For software teams that rely on relational databases, the ability to do SQL performance tuning is much needed. Follow us on this advanced SQL series to pocket yourself some handy query tips.

Introduction

Indexes are a common way to enhance database performance. Small, fast, and optimized for quick lookups, an index allows the database server to find and retrieve specific rows much faster than it could without one.

With examples, we will provide you with a closer look at SQL indexes.

SQL Index 101

This is a query to find employees by their id:

SELECT *
FROM employees                 
WHERE employee_id = 57

And its query plan:

QUERY PLAN                                                                                         
----------------------------------------------------------
Seq Scan on employees  (cost=0.00..268.00 rows=6 width=73)
(actual time=0.020..1.281 rows=1 loops=1)
 Filter: (employee_id = 57)                                                                       
 Rows Removed by Filter: 9999                                                                     
Execution Time: 1.311 ms

As shown in the query plan, Seq Scan (or Full Table Scan) has been used for the query. Thus, the system would have to scan the entire employee table to find all matching entries. This is clearly an inefficient method. In this case, let’s try adding a new index:

CREATE UNIQUE INDEX employee_pk ON employees (employee_id);

And voila:

QUERY PLAN                                                               
---------------------------------------------------------------------------
Index Scan using employee_pk on employees  (cost=0.29..8.30 rows=1 width=73)
(actual time=0.030..0.030 rows=1 loops=1)|
 Index Cond: (employee_id = 57)                                         
Execution Time: 0.050 ms

You can see that the new index has reduced significantly the execution time of the query. The database does not need to scan the entire table for the matching entries but only in the index.

But how does the index improve its performance?

An index was built using a combination of B-Tree and Doubly Linked List. B-Tree is a data structure that allows us to quickly search for the matching node (O(log n)). The Doubly Linked List builds the logical order between the leaf nodes. Let’s take a look at the following tree: