Jose: Hi guys and welcome back.
In this video we're going to learn
how to use SQL to create an index.
We've decided not to go with a presentation video
in this case because the create index
is so similar to create table that really
there's not much difference,
so we thought we'd spare you the presentation
and instead we would jump directly into db browser
to show you how to create an index.
So let's get started.
Something important to remember, though,
that we've measured before is that you should not
jump into adding indices to a database.
You should first think how often you're gonna read
and write from the table and that includes
searching and filtering for data.
If you're gonna read and filter and search a lot more
than writing, then it's maybe worth having.
For the sake of learning though,
we're going to add an index to our release timestamp
column here in our movies.
So the release timestamp column,
let's say that we're doing a lot of searching
with release timestamp.
We're always looking at which movies we're waiting for
and so forth so we want an index in here
to speed up the queries.
Totally acceptable explanation but obviously in this case,
we wouldn't be adding a query for this application
because clearly it's very small,
we're only using it here but as the application grows,
maybe you'll wanna do things differently.
Totally acceptable.
Just remember to add the index when you need it
and not really before.
But, nonetheless, to execute SQL here,
we're gonna add an index that is create index,
exactly like create table
but we're creating an index instead.
Remember that we mentioned that an index is a table.
All the data for how the index is ordered
is stored in a table.
So just like create table, we have to provide a name
for the index, for example, idx movies release.
Normally you prefix index names with idx on this course,
not necessary though, but it's the convention.
Then, differently to create table, we have to say
on which column and table we're going to apply the index.
So we are applying it on the movies table.
And then in brackets, the column.
So we do release timestamp.
So that's it.
We create index.
We give it a name.
And we say on which table and column we're gonna apply it.
You can also do a couple other things.
You can create a unique index and that's gonna ensure
that any values for movies release timestamp
have to be unique.
That's the assumption when you do this.
Also remember that when you create a primary key,
that normally creates an index for you as well.
And so the ID column of the movies table for example,
already has an index associated with it.
You don't have to create another index.
Okay.
So this is all we need.
Now you can run it here if you want
but I'm gonna cut it and we're gonna run this
directly in our application.
So we're gonna go over to database.py.
We're gonna add our query here.
And the reason why you might wanna add it here
is so that you can create the index
when you create the tables so that you don't miss it
in case that the database gets deleted.
So we will add a new query here.
We're gonna create release index, and that's gonna be this.
Do note that because this is running every time
the application starts,
you wanna do create index if not exists.
Just like creating a table, you add the if not exists there.
That's only going to create it if it's not there already.
And that's it.
Then you copy that and we copy this and we put it in there.
Now we're creating the index
every time we create our tables.
Of course, make sure that this happens after creating
the movies table otherwise the index would try to be created
on something that doesn't exist yet.
So we're gonna save this.
We are going to close db browser.
We're gonna delete data.db.
And then we're gonna run the application.
As you can see, it runs.
Therefore, we didn't get an error when running the query.
Everything worked.
And now, you know, searching for the upcoming movies
is gonna be slightly faster, unnoticeable, though
for this size of application.
But if you were doing something really complicated,
something really large, a lot of rows,
then the index would substantially help speed things up.
But that's about it for now.
There's a lot more to learn about indexes
such as how to remove them, how to use multi-column indexes
and so forth, but you've got the gist indices,
how they work and we're not going to be cumbersome
of those other edge cases in this course.
Thank you guys for joining me.
Thanks for watching this video.
I'll see you in the next one.