High Performance
SQLite
Introduction
Introduction to this course
Introduction to SQLite
SQLite is safe
It's just a file
Running SQLite locally
Good uses for SQLite
SQLite's limitations
The documentation
SQLite Internals
SQLite's structure
SQLite's file format
Dot commands
Pragmas
Virtual tables
Schema
Flexible types
Types
Strict types
Dates
Booleans
Floating point
Rowid tables
Auto increments
Without rowid
Generated columns
Optimizing SQLite
Locking
Rollback mode
WAL mode
WAL vs Journal benchmarks
Busy timeout
Transaction modes
Vacuum
Analyze & Optimize
Suggested pragmas
Faster inserts
Indexes
Introduction to indexes
B+ trees
Primary, secondary, and clustered indexes
Without rowid tables
Benchmarking without rowid tables
Imposter tables
Primary key data types
Where to add indexes
Index selectivity
Composite indexes
Composite ordering
Covering indexes
Partial indexes
Indexes on expressions
Automatic indexes
Duplicate indexes
Indexing joins
Advanced SQL
Explain
Index obfuscation
Joins
Subqueries
Unions
CTEs
Recursive CTEs
Window functions
Dealing with NULLs
Row value syntax
Indexed sorting
Upserts
Returning
Aggregates
Triggers
Operating SQLite
Altering schema
Altering schema with tools
Multi-database
Multi-tenancy
Backups
Exports
How to corrupt SQLite
Advanced JSON
Intro to JSON
JSON vs. JSONB
Argument types: path, value, JSON
JSON5
Valid JSON
Creating JSON objects + arrays
JSON Extraction
Updating JSON
JSON aggregates
JSON table functions
Indexing JSON
Full Text Search
Creating FTS tables
Performing searches
Ranking results
Highlighting matches
Bonus Interviews
DHH discusses SQLite in Ruby on Rails
Distributed SQLite with Litestream and LiteFS
Offline-first, multiplayer SQLite
Production SQLite with Turso and libSQL
Migrating from Postgres to SQLite with Kent C. Dodds
Ruby on Rails with SQLite
Moving from Redis to SQLite with Mike Buckbee
Locked video

Please purchase the course to watch this video.

Video thumbnail
Indexes
Indexes on expressions

Full Course

$
179
$229
USD, one-time fee
Aaron's course offered invaluable insights into using SQLite on the server side, boosting my confidence to implement it in our company's projects.
Alexis Trechau
Alexis Trechau

SQLite for Production

Build with and integrate SQLite into your production applications.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Turso

Summary

I learned that you can create an index on expressions in addition to columns, which can be very helpful for optimizing database queries. By demonstrating how to index the domain part of an email, I showed the practical steps involved, including using the substring function to extract data. This technique can be particularly useful for filtering or reporting, such as finding users with specific email domains or indexing specific keys from JSON data.

Video Transcript

So far, we have just been putting indexes on columns, which obviously. Right? Well, good news. You can put index on expressions as well. I'm gonna show us how to do I'm gonna show you how to put an index on the domain part of the email column.

We have a couple of options here. You could create a generated column like we like we did back way early on. You can create a generated column and create it either as virtual or as stored and then put an index on that virtual or stored column. Either 1 doesn't matter. But instead, we're just gonna put an index on the expression directly.

Putting an index on a generated column is just a matter of putting an index on any column. It's no different. So I'm gonna show you how to put it on an expression directly. Let's go. The first thing I wanna do is we gotta figure out what this expression is actually gonna be.

So if we do select star in fact, let's do select ID email from users limit Let's just do limit 5. There shouldn't be Yeah, it's a lot more compact when we have as much smaller tables. So we have all the Aaliyah's here at the beginning. So let's start building up this expression. Let's first look for in the email column, let's look for the at sign.

Great. That gives us the character where it is. And then we can say, substre email. This is gonna be off by 1, I feel like. Yeah.

That's pretty close. In fact, it was off by 1. There we go. So let's clear this out. Make some space.

Did we do this right? So champlin.netchamplin.netjacoby.orgjacoby.org. This is the library that generates this stuff. I don't know where it gets its data. Green.info, green.info.

Alright. So that's our expression. We have our expression as substringemailinstringemail@signplusone. Great. We can just create an index on it.

So let's make some space. We'll do create index, email domain on users. And did I okay, good. It's still there. I just wanna copy that out and I'm just gonna put that right in there.

So we're just gonna put an index on that expression. It's gonna run for a little bit and we see dot indexes. We see email domain pragma index info email domain. That's a little bit that's that's not that's not that helpful. So, yeah.

That's fine. We'll just carry on. We do see it in the indexes as email domain. So the real question is if we do select star from users where this thing equals, what was 1 of them? Green dot info?

Green dot info limit, that's fine. Limit 3. And that looks like all the green dot infos. Okay. I have my widths here all set up to make this table look a little bit nicer.

So I'm gonna run that and then run this query again. There we go. So we've got all of the green dot infos. So we don't know that the index worked. We just know that the query worked in that it gave us the correct results.

So dot EQP on and then we'll run this again. There we go. Look. Using index email domain expression. That's the expression that we wrote earlier equals green dot info.

So now we have put an index on an expression and this can be very very useful when you're trying to extract some data. So here we were doing it on the email domain. You can imagine that being very helpful if you wanna filter out or do some sort of reporting or you want gmail gone or you want at apple.com. You wanna go find users that are that work at apple. That could be index assisted now.

I think the more interesting use case, not that that's not interesting, the more interesting use case is taking a giant JSON blob and extracting 1 or 2 keys and indexing those. Something like Postgres has, I think they're called ginindexes, GIN, ginindexes, where you can just like throw an index over a whole blob of JSON. SQLite doesn't have that. MySQL doesn't have that. But what you can do is if you have some specific keys that you care about in your JSON blob, you can write an expression to pull them out, put an index on that expression, and it's as if you've indexed JSON when really you're just indexing the result of an expression, but it's still pretty awesome.