Simple Tweaks That Make SQLite Lookup Faster – Part 1

#sql #sqlite #webdev #programming

Today I encountered issues with FreeDevTools online (a free, open-source hub where developers can quickly find and use dev resources without any hassle of searching all over the internet), where I moved static pages to an SQLite database, but it was taking too long to fetch pages based on URL requests.

To improve fetch speed efficiently, I started learning about SQLite by going through the official docs, where I learnt how SQL queries work, how to plan queries, and how to design an architecture to fetch data faster.

Before going into the details, let us first understand what SQLite is.

SQLite is a lightweight, serverless, self-contained, and zero-configuration relational database management system (RDBMS) that stores data in a single, cross-platform file.

In this series I am going to explain how SQLite works with actual examples that you can also try practically to understand.

What is Lookup?

It is the process of searching for specific information or data within a system, database, or list using a particular input or key.

The Goal is to quickly retrieve the corresponding value or record that matches the given criteria.

In SQLite, search works based on rowId. rowId is a special column that every row in SQLite has by default.

rowid can be used to uniquely identify a row within the table.
The rowid(s) are always unique. They may not be consecutive but always in strictly ascending order.

We need a huge dataset to actually find how lookup actually works.

Setup SQLite DB

I have used the movie dataset Link

It has 24 columns. For our convenience, let us focus only on these columns:

Creating Table

CREATE TABLE Movies (
    id INTEGER,
    title TEXT,
    status TEXT,        
    release_date TEXT,   
    budget INTEGER,
    revenue INTEGER
);

Insert all data into the new Movies table

INSERT INTO Movies (id, title, status, release_date, budget, revenue)
SELECT 
    id, 
    title, 
    CAST(status AS TEXT),     
    CAST(release_date AS TEXT), 
    CAST(budget AS INTEGER),   
    CAST(revenue AS INTEGER)   
FROM TMDB_movie_dataset_v11 tmdv ;

SQLite Searching

Tables Without Indices

I want to find the budget and revenue of the Fight Club movie.

SQLite reads every row out of the table, checks to see if the movie column has the value Fight Club, and if so, outputs the budget and revenue from that row.

This algorithm is called a Full Table Scan.

In a full scan, the entire content of the table must be read and examined to find the needed row(s).

This query took almost 140ms - 150ms

Select "budget" , "revenue" From Movies m  Where title = "Fight Club"

Now let us check what the millionth row duration will be.

Select "budget" , "revenue" From Movies m  Where title = "Looking Back on the West March"

This makes it clear that this query is time-consuming, as it goes to each row and then gives the result.

To skip the full table scan, we need to optimize the query.

Lookup By Rowid

To avoid a full DB scan, we can lookup by rowid. Since rowid is an integer and is ordered, SQLite uses binary search to find the row using rowid.

Here is how it works.

Select  "budget" , "revenue" , "rowid"  From Movies m  Where rowid = 8;

Comparing all together.

What's Next?

From the above small query improvement, we understood how SQLite actually fetches data.

There is a detailed explanation on how these SQL queries work Link.

In the current implementation, if we have the rowid, we can fetch faster. What if we don't know which rowid contains the Fight Club budget and revenue?

In the next blog post I will explain how we can find budget and revenue of Fight Club using only the title of the movie.