# Adding indexes to our app
Before jumping in an adding indexes to a database, you must think about how often you'll read and write from it. That includes searching and filtering for data.
In addition, you should often only add indexes to a database when required; when your queries are taking too long.
This will often only happen when you have a lot of data already in a database.
But for the sake of learning, let's add an index to our
movies.release_timestamp column so that when we're searching for upcoming movies it'll be a bit quicker.
CREATE INDEX idx_movies_release ON movies(release_timestamp);
This has three parts:
CREATE INDEXtells the database we want to create an index.
idx_movies_releaseis the name of the index table we'll create.
ON movies(release_timestamp)is for which table and column we want to create the index.
If two movies could not be released at the same time, then you could do
CREATE UNIQUE INDEX for extra performance. However in our case, that doesn't make sense.
In most RDBMS's,
PRIMARY KEY already creates an index, so you don't need to create indexes for your primary keys.
# Adding the index code to our app
We might want to create the index right after creating the tables.
The index will be kept up to date when new data is inserted.
Let's start off by creating the query that we'll run:
CREATE_RELEASE_INDEX = "CREATE INDEX idx_movies_release ON movies(release_timestamp);"
And then in the
create_tables() function, we can call it:
def create_tables(): with connection: connection.execute(CREATE_MOVIES_TABLE) connection.execute(CREATE_USERS_TABLE) connection.execute(CREATE_WATCHED_TABLE) connection.execute(CREATE_RELEASE_INDEX)
That's about it for now! There's more to learn about indexes, such as how to remove them, or how to use multi-column indexes. However, we won't be covering how to do that in this course.