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.