Indexes are a very useful feature of SQL databases.
They can be created easily and can significantly improve the performance of your queries.
If you want to know all about indexes, from the basics of what an index is,
the different types of indexes, and even some tips on when you should create an index, keep watching.
In this video, I’ll share a simple explanation of what an index is in an SQL database. We’ll look
at how to create a simple index including the SQL scripts for Oracle, SQL Server,
PostgreSQL, and MySQL. We’ll look at a few different types of indexes,
and finish off by looking at some tips on when indexes have the most impact.
Let’s get into it.
Have you ever looked at a big book, like a textbook, and tried to look for a specific topic?
Let’s say you have a textbook on the history of human civilisation,
and you wanted to find the section on Julius Caesar.
You might have some idea of what page this topic is discussed.
It was in Roman times, so it might be somewhere in the middle of the book? You flip somewhere
to the middle and look for it, and keep scanning pages until you find it mentioned.
Or, you read the entire book looking for information on this one topic.
You would start on page 1 which discusses the
start of human evolution and read every page until you get to the current year.
That would take a long time, so you probably wouldn’t do it.
We can agree it’s not a very efficient way of finding what you need.
Reading every page until you find the topic you’re looking for is
equivalent to the database’s method of finding specific records in a table.
When you run a query, the database looks through
every record in the table to find the records you need.
That is, unless you have an index.
Let’s go back to our textbook example. Let’s say this textbook had a section at the very
end of the book called “Index”. It listed all of the topics that were covered in the book.
This could take up a lot of pages, depending on how big the book is.
But, in this index section, there would be an entry for Julius Caesar.
This shows the topic, as well as the specific page numbers where this topic is mentioned.
Now, all you need to do is turn to those page numbers and learn more about Julius Caesar.
This is the same way that indexes work in databases.
So what is an index in SQL?
An index is an object in the database that allows you to find specific data in a table faster.
Like the index section in a book,
they take up a bit more space, but the benefits that they bring are often worth it.
Also, like in a book, indexes work best when you have a specific term to search for.
You don’t need to ask the database to use an index if it’s already there.
Most of the time, the database will work out that it needs to use the index.
There are several different types of indexes. I’ll explain some of the more common ones
in this video, and mention others if you wanted to look further into them.
The first type of index is probably the most common.
It’s called a “b-tree” index. It’s also the default type of index, which means it’s the one
that is created if you don’t add any modifiers to the statement, which we’ll look at shortly.
B-tree stands for “balanced tree”. It’s called a tree because of the
way the index is used by the database. It works like a tree with branches and leaves.
So, how does it work?
Let’s say we’re looking for a record in a table with a specific ID value.
The ID is a column in this table and it has a b-tree index on it.
A b-tree index would look like this.
The steps to find the record with an ID of “B” would be:
Step 1: Look at the first level of the index. Find the entry, or node on this level,
that covers the value of “B”. There is only one here (the “A” at the top).
Step 2: Move to the second level of the index that comes from the
first level identified in the previous step.
Step 3: Find the entry, or node, that includes the value of “B” in its range. In the example above,
we have two nodes on the second level: “A” and “N”. “A” covers the values starting with “A”
and ending with “M”, because the next value is “N”. “N” covers the range of values from
“N” to “Z”. It’s a simple example, and real values would be more complicated,
but it demonstrates that the possible values (A to Z) are split between A-M and N-Z.
Step 4: Move to the third level of the index that was identified in the previous step.
Step 5: Find the entry, or node, that includes the value of “B” in its range. The nodes are
labelled here as A, G, N, and R. Which one is it? It’s the “A” node, because that covers the
range of “A” to the just before next node (value of G) which means it ends at “F”.
Step 6: Find the record that matches ID “B” and return that record.
As you can see, there is a repeating sequence of “look in the range of values”
and “go to the next level”. This is repeated for the number of levels in the index.
In most indexes, even those with millions of records, there are usually less than five levels.
So, we’ve just looked at how they work. But how do you create one of these b-tree indexes?
You can use the Create Index command. It looks like this.
There are only a few things you need to specify to create a b-tree index.
First, the index name, which is the name of the index you are creating.
This must be a unique name on the database.
Next, the table name, which is the name of the table you’re creating an index on
Finally, the columns.
This can be a single column or a comma-separated list of columns you want to create an index on.
This statement is part of the SQL standard so it will work on Oracle,
SQL Server, MySQL, and PostgreSQL, as well as many other databases.
Let’s look at an example.
This code would create an index called idx_emp_id on the id field in the employee
table. The index name starts with idx, which is not required, but is a technique I like to follow.
Adding a prefix of idx or ix to the start of an index name
makes it easy to tell when looking at the output of other database statements.
This is a b-tree index, as that’s the default type of index in Oracle,
SQL Server, MySQL, and PostgreSQL. In SQL Server it’s also known as a non-clustered index.
Later in this video I’ll explain when you would create a b-tree
and when you would create other types of indexes.
Another type of index is a function-based index.
This is an index that is created on the results of a function or an expression.
This is a little different to a b-tree index, because a b-tree
index on a column is not used if the query performs a function on that column.
If a function-based index is created, then the index can be used.
Let’s see an example.
Let’s say we have a query to find all employees that have a monthly salary greater than 10,000.
To find the monthly salary, we divide the annual salary by 12.
Now, let’s say there are no indexes on this table. We can run this query and it will work,
but it might be slow.
To make it faster, we can create an index on it. The query is using
the annual_salary in the WHERE clause, so let’s create a b-tree index on this column.
When we run the SELECT query again, it may still be slow. The index we created won’t be used.
Why is that?
It’s because the index is on the annual_salary column,
and the value being compared against in the query is actually the result of the annual_salary/12.
This means any query that uses a function or expression on a column in the where clause
will almost never use an index created on that column.
This is where a function-based index comes in.
It’s similar to creating a b-tree index. You just add your function
or expression where you add your column.
So, in our example the index would look like this.
We have the annual_salary / 12 inside the brackets after the employee table.
If we run the original Select query, it should use this new index, and be a bit faster.
Some other situations where you may want to create a function-based index
are if you’re comparing strings using UPPER or LOWER functions,
or if you want to limit data based on a specific date component such as dates within a month.
If you work with SQL Server you may have heard of another type of index called a “clustered index”.
What does this mean?
This is a type of index that defines how the data is stored for the table. A clustered index
specifies that the data in the table is stored in the same order as the data in the index.
This means that it is faster to retrieve the data when a clustered index is used,
because there’s less for the database to do.
The drawback is that there can only be one clustered index on a table. The alternative
is to create a non-clustered index, which is the default type of index that we saw earlier.
To create a clustered index in SQL Server, we add the word CLUSTERED before the word Index.
Here’s an example. We can see the Create Clustered Index command here
which will create a new clustered index on the table.
Another type of index is a bitmap index. A bitmap index looks like a two-dimensional
map or table of values. And, as far as I know, bitmap indexes are only available in Oracle.
Each row is a separate identifier for the row, and each column relates to a separate value of
a specific column. The intersection of rows and columns is a single bit,
which indicates that row has that value in the column.
Let’s see an example. Let’s say we have an employee table with a column called status,
and this status column can be set to either Active or Inactive.
A bitmap index could be represented like this.
Each row represents a row in the table,
and we have a column for Active and a column for Inactive.
This is how a bitmap index is represented. It works well for columns that have a small number
of unique values. In this example, there are only two values: active or inactive. The more possible
values in a column, the more data that is stored for the index, and the less effective it would be.
How do we create a bitmap index?
We use the same Create Index statement, but with the word Bitmap in the middle.
This will create a new bitmap index on the specified column.
As I mentioned earlier, this only applies to Oracle databases,
and only works well in some scenarios, so it may not be a type of index you use very often.
There are many other types of indexes in different databases,
like fulltext indexes and spacial indexes, but we won’t go into them in this video.
What’s useful to know is when to create an index. Indexes aren’t a magical solution to
solve all performance problems with your query, but they can be helpful.
There are a few scenarios where you can create indexes that can help speed up your query.
First, consider creating indexes on columns that are in your WHERE clause,
as these are columns that the database will search within.
The same can be said for JOIN clauses: consider creating indexes on columns in the JOIN clause.
If a column only has a few distinct values, then creating an index on it
may not improve performance. For example, a status column that has 2 or 3 different values.
Consider indexing foreign key columns, as they are not indexed automatically.
They will likely be used in joins and covered by the earlier tip, but it’s something to consider.
You might be wondering, can you just create indexes on every column? You could, but it’s
generally not advised. An index can help with the performance of select queries, but can slow down
Insert, Update, and Delete queries, because the database needs to update the index for each of
those operations. It’s much better to be specific about the columns you want to create indexes on.
And that brings us to the end of this video on indexes in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the fact that indexes help columns in JOIN
conditions and WHERE clauses, or a few of the different types of indexes, or something else?
Thanks for watching.