Simple Tweaks That Make SQLite Lookup Faster - Part 2

Hello, I'm Ganesh. I'm working on FreeDevTools online, currently building a single platform for all development tools, cheat codes, and TL; DRs — a free, open-source hub where developers can quickly find and use tools without the hassle of searching the internet.

In Previous blog, I explained how SQLite works and how to improve lookup speed. I took a big data set to understand speed of fetching data from sqlite.

Finally impoved lookup speed by using rowid. But it was not ideal for lookup as we don't know which rowid our data is stored.

As I took movie data set as example in previous blog post also I will going to take same data set and I will explain how we can fetch data from sqlite using only keeping in mind of practicalilty.

To have some context about impracticallity of rowid

Lookup By Index

The problem with looking up information by rowid is that you probably do not care what the budget and revenue of "Movie 8" is - you want to know the details of movie Fight Club. And so a rowid lookup is not helpful.

To make the original query more efficient, we can add an index on the "Title" column of the "Movies" table like this

CREATE INDEX Idx1 ON Movies(title);

Now this lets use same query.

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


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

Previous Dutation of db fetch