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 INDEX tells the database we want to create an index.
• idx_movies_release is 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.

TIP

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.