A poorly optimized database can drag your entire application down with it. 😬

Ensuring optimal database performance is crucial for enterprise applications, but achieving it can be a challenging task. When it comes to relational databases, specifically those using SQL, improving performance might be more straightforward than you might think. While numerous factors can influence SQL database performance, this article will focus on one of the most common and impactful optimization techniques used right now to enhance database query operations…table indexes.

Indexes are special lookup tables that need to be used by the database search engine to speed up data retrieval. An index is simply a reference to data in a table. A database index is similar to the index in the back of a journal. It cannot be viewed by the users and just used to speed up the database access.

But most of the time, when we are considering creating indexes, we tend to only look at the column index part. This is not bad, because it is always good to add an index to your columns, but there is also another approach to adding indexes, another type of index.

Let's talk about Functional Indexes!

⁉️ What are functional key parts?

A functional index is an index in a database that is based on the result of a function applied to one or more columns of a table. Functional key parts can index expression values. Hence, functional key parts enable indexing values that are not stored directly in the table itself.

The theory behind SQL functional indexes is the same. When the database is executing a query, it uses the relevant index like a pointer, enabling it to read only the relevant rows of data to find the information you’re searching for, instead of having to read the entire table.

⁉️ When are functional indexes helpful?​​

Functional indexes are helpful when the query retrieves data based on the result of a function. It can be useful when the function requires high computational power to execute.

In our examples, we will use functional indexes to increase performance in an SQLite database.

Common use cases for functional indexes in SQLite include case-insensitive searching, date calculations, and full-text search. However, functional indexes in SQLite have some limitations, such as the function used in the index must be deterministic and must always return the same result for the same input.

Syntax

Let's run a simple query to find all film titles in lowercase from movies database.

SELECT lower(title) FROM film;

If we run an explain operation on this query, we will get the following result:

EXPLAIN QUERY PLAN SELECT lower(title) FROM film;

As we can see from the details, the query is executing a scan of all films from film table.

Here is how you can define functional indexes in a film table to search for lowercase film titles:

CREATE INDEX lower_movie_title ON movie (lower(title));

Now, if we run the same explain query:

EXPLAIN QUERY PLAN SELECT lower(title) FROM film;

We will get…

Awesome! Our query has made use of the index to retrieve the results.

Once you create an index, queries should run much faster because the database will use the index to find the data it needs instead of scanning the entire table. This means that the database has to read less data from the disk and process fewer rows, which speeds up the query.

Functional indexes can improve the performance of some queries, but they can also make it slower to insert or update data in the table because the index needs to be updated every time the table is changed. This can slow down applications that write a lot of data, so use functional indexes carefully.

📕 References

Great video from Planetscale: Increase SQL performance with this technique (10 examples)