SQL Database Index Basics
Tags: sql, mysql, beginners
Published: 2024-01-04 14:59:11

If you are just starting out on your SQL database journey, you may be wondering what indexes are and why we care so much about them. 

While the subject of indexes can be complex, learning some basics will get you on your way.

## What are indexes?

Much like an index in the back of a book, database indexes help speed up queries by knowing where to look for the data you are after. 

If a book didn't have an index, you would need to look through every page until you found what you were after, but having an index in the back of the book means you can look up what page something is on and go straight to the page, which speeds up your search.

This is the role indexes have in a database.

## What types of indexes are there?

There are four types of indexes.

**Primary**
A Primary index is a unique index. It ensures that the field that is set as Primary will contain a unique value in every row of a table.

A table can only have one primary index. 
 
**Unique**
Much like a primary index, a unique index ensures that the field value is unique in every row of the table. 

There can be many unique indexes in a table. 

**Index**
This type of index is a general index. It tells the database to make an index of the data in the field, which it may use to speed up queries. Field values do not need to be unique.

**Fulltext**
While the other indexes index on the whole value in the field, full text indexes index on text within each row of the field set as full text. This allows the index to work on searches for partial text. It's particularly useful on fields set as TEXT or BLOB data types where large amounts of data may be stored in the field.

## When should an index be added?

It is likely that queries that take longer than it should to run need an index. Inefficient queries can bog down your server and lead to increased server load, long response times, and poor server performance. 

Adding indexes to tables with small amounts of data in them won't help speed up the query. What you will most likely find is that the indexes are ignored. 

Before adding an index, make sure it really needs to be indexed. While indexes will speed up SELECT queries. It can slow down INSERTs or UPDATEs. 

**While it may be tempting to index every field in a table. This is not good practice and can lead to poor server performance.**

If a query runs well without an index, it may not need an index, but it if runs slow and bogs down your system, its probably a good idea to add one. 

Use the EXPLAIN keyword in your query to get an insight into how a query will run, this may give you information you need to make informed choices on indexing. 

## What is EXPLAIN?

EXPLAIN will show you what the database is planning to do to retrieve the results from your query. This is called a **query execution plan**.

If you have a query like:
```SQL
SELECT * FROM employees WHERE gender='f';

```

To use the EXPLAIN keyword to look at the query plan you would add EXPLAIN to the beginning of your query

```SQL
EXPLAIN SELECT * FROM employees WHERE gender='f';

```

Which will show a result similar to:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vaovjtkkmzm3vhfr1ll7.png)

## Temporary Tables and File Sorting
Using EXPLAIN, sometimes you will see **Using Temporary** or **Using File Sort**. These are both things to watch out for, there is a good chance the query is not optimized well if the query plan contains these.

**Temporary Tables**
While temporary tables are sometimes required to run a query, if SQL has decided that creating a temporary table is the best option, it's an indication that it's worth taking a second look at the query. 

Sometimes the way to prevent the need for the temporary table  is to add an index, but don't just blindly add indexes (we want to keep the number of indexes on a table to as few as practical), first take a look and see if there is another way you could write the query that doesn't require a temporary table.

**File Sorting**
File sorting means that SQL is trying to sort all the results in a temporary buffer, which can slow the query down. 

Indexes can help with removing file sorting, but as with temporary tables, look to see if there is a way to improve your query before adding in index.

## Using an index on a WHERE clause
For this tutorial I am using a sample database available from [https://github.com/datacharmer/test_db](https://github.com/datacharmer/test_db).

Looking at the WHERE part of a query is a good place to start when first starting out with indexes. If a field referenced in the WHERE clause has an index, the query will try to use that to speed up the query.  

```SQL
SELECT * FROM employees WHERE first_name='mary';

``` 

The EXPLAIN for this query looked like this:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vaovjtkkmzm3vhfr1ll7.png)

Explain shows us that it used the WHERE to execute the query since there is no index to use. It also shows that it looked through 299556 rows to find the data. This is a **full table scan**. It's looked through every row and to see if the value of the first name field is equal to _Mary_. 

Full table scans are a good indication that an index might be needed, especially in large data sets.

```SQL
CREATE INDEX first_name_idx ON employees (first_name);

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yokykfgnd3hffbk2kzfa.png)

You can see from explain that it used the index this time and only looked at 224 rows rather than all 299556 rows. 

This makes the query much faster. 

This is what indexes are good for. 

## Multi-field indexes
Indexes can be created to combine multiple fields. In this example we might want to run queries on first name and last name.

```SQL
SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name='Cooley'

```

Without an index this would result in a table scan and explain would look similar to the previous table scan
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vaovjtkkmzm3vhfr1ll7.png)

We could add indexes on both first name and last name fields separately. 

```SQL
ALTER TABLE employees
ADD INDEX first_name (first_name),
ADD INDEX last_name (last_name);

```
This would provide a decent index for the query we have run, but not the best index.

 Explain would provide an interesting result.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mu7dtqubjk8637hovw4s.png)

The database has merged the two indexes together then looked for a row matching both fields combined. 

If instead we create an index combining the two fields, then the database doesn't need to merge the indexes and it will be a less taxing query. 

```SQL
ALTER TABLE employees
ADD INDEX full_name (first_name,last_name);

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ervkdj574jpvg5h2p7kz.png)

This is a much better looking query execution plan and will run faster than it did with separate indexes. The overhead of merging the indexes together takes time, so anything we can do to help the database execute our queries faster is a good thing. 

**Special Note**: The order fields are added to a multi column index matters in some cases

This query: 
```SQL
SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name='Cooley'

```

Could be rewritten as 
```SQL
SELECT * FROM employees 
WHERE last_name='Cooley' 
AND first_name='Mary'

```

This will be fine, it will use the index, as long as both fields are present in the WHERE clause.

But what happens if we want to run a query on just on first name? 

```SQL
SELECT * FROM employees 
WHERE first_name='Mary'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6n28dxakby96exmqxlqb.png)

Even though only one field in the index has been used in the query, it still uses the index.

What happens if we want to run a query on last name?
```SQL
SELECT * FROM employees 
WHERE last_name='Cooley'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ju9xmorr4qegn76art9.png)

You can see that it is no longer using the index. 

So what happened here?

Multi field indexes cascade from left to right. For the index to be used, the first field in the index needs to be in the query before it will look for the presence of the second field in the index.

To gain the advantages multi-field indexes give us and also run queries on last name alone, an additional index could be added for just last name

```SQL
ALTER TABLE employees
ADD INDEX last_name (last_name);

```
**Note**: There is no need to add an index for first_name since the multi-field index will work with just the first name.

If we rerun the original query

```SQL
SELECT * FROM employees 
WHERE first_name='Mary' 
AND last_name='Cooley'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/otosltml3fgzrp0ze0ya.png)

If we look at the fields **Possible Keys** and **Key**, it will show that it has considered both indexes but chosen the multi-field index

If we rerun the query looking for just last name

```SQL
SELECT * FROM employees 
WHERE last_name='Cooley'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ky95rlt9iu304sturici.png)

This time it will use the index we created for just last name.

## Indexing on ORDER BY
WHERE clauses are not the only place indexes are used. Indexes are also used when ordering data in the query.

```SQL
SELECT * FROM employees 
ORDER BY last_name LIMIT 100

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hnekd3euzj9bcta7fe0p.png)

Even though there is no WHERE clause, the index is used to order the data faster.

## Indexing on GROUP BY
Indexes are also used in GROUP BY queries. 

```SQL
SELECT COUNT(*),last_name 
FROM employees 
GROUP BY last_name 

```
If this query is run without an index Explain gives us this result:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xga3iqen2hfjab0fvve1.png)

This is possibly the worst kind of query. It's using a file sort and it's creating an internal temporary table to execute the query. This will cause the query to be quite slow.

Rerunning the query with an index on last_name added:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b24mxwpjtxu9wggnt3co.png)

Now it is using the index and will be a much faster query.

## Indexing with LIKE

In some cases indexes will not be used with a LIKE keyword.

For queries similar to the the ones below, the index can be used:

```SQL
SELECT * FROM employees WHERE last_name LIKE 'cooley'

```

```SQL
SELECT * FROM employees WHERE last_name LIKE 'coo%'

```

However, queries similar to the ones below will not use the index:

```SQL
SELECT * FROM employees WHERE last_name LIKE '%ley'

```

```SQL
SELECT * FROM employees WHERE last_name LIKE '%ole%'

```

The index is used up until it hits a wild card, after that, it no longer uses the index. 

In cases like these, it may be more beneficial to use a full text index and query the full text data rather than using LIKE.

## The 30% Rule

Sometimes SQL will not use an index even when you think it should. To demonstrate this I have added an index on the _hire_date_ field

```SQL
ALTER TABLE employees
ADD INDEX hire_date (hire_date);

```

```SQL
SELECT * FROM employees 
WHERE hire_date = '1991-06-01'

```

This query will use the index just fine
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ecm6vzp7cg5257vp846.png)

But what if I want to find all the employees hired in 1991?

```SQL
SELECT * FROM employees 
WHERE hire_date BETWEEN '1991-01-01' 
AND '1991-12-01'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qb6da0i2laxbrmsv9aas.png)

Looks good also, it used the index. 

Now we want employees hired between the start of 1987 and the end of 1991

```SQL
SELECT * FROM employees 
WHERE hire_date BETWEEN '1987-01-01' 
AND '1991-12-01'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jo5ejiy5vmfh7ark5pgj.png)

This query does not use the index. 

Whats going on here?

The answer lays in the amount of rows being returned. When the number of rows returned goes over about 30% of the total rows, SQL is less likely to use the index.

Sometimes this is OK, other times it is a problem that needs to be addressed.

One option is to paginate your results by limiting the results returned

```SQL
SELECT * FROM employees 
WHERE hire_date BETWEEN '1987-01-01' 
AND '1991-12-01' 
LIMIT 1000

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5e9skspvv26wuunzgivj.png)

## Creating good queries without an index

Consider the following query

```SQL
SELECT * FROM employees 
WHERE first_name = 'Mary'

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ee9ylvldza4vyorctqp.png)

In this query, first_name does not have an index and the result is that a table scan is done to retrieve the results

An index could be added to prevent the table scan and that would fix the issue.

However, Before we create the index, we should see if there is another way we can query the database that won't result in a table scan. 

One option is to paginate the results. We can do this by limiting the result set.

```SQL
SELECT * FROM employees 
WHERE first_name = 'Mary'
LIMIT 10

```

At first glance you may conclude that this would prevent the table scan because it's only returning 10 results.

However, this isn't the case. 

Looking at the execution plan, it looks exactly the same as before.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e2zj6hfvr87mnlzxuk13.png)

This is because even though it returns only 10 rows, The execution plan has determined that a full table scan is the best way to go. 

To prevent this table scan, there are a few options, but one simple method is to order the data by the tables primary key


```SQL
SELECT * FROM employees 
WHERE first_name = 'Mary'
ORDER BY emp_no
LIMIT 10

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oduh3drg26uhypwda7oa.png)

Now it is only looking at the number of rows it needs to return the number of results asked for.

By paginating the results, there is a good chance that result offsetting will be required.

```SQL
SELECT * FROM employees 
WHERE first_name = 'Mary'
ORDER BY emp_no
LIMIT 10,100

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mafvsq05xevgdywd0ryo.png)

The execution plan shows it is looking in more rows than it does without the offset, The plan is telling us that it will scan only up until it finds the the 10 rows it is looking for, and not scan the whole table. 

While adding an index would provide a faster query, to prevent having too many indexes, you can adjust your query to prevent the need for the index. This is all part of balancing adding indexes with adjusting queries to be more efficient queries. 

## Final Thoughts
Indexes will help optimize your queries, but you do need to balance adding indexes with improving your query instead.

Try to improve your query before adding an index, adding too many indexes can slow down inserting and updating data into your table. 

Use EXPLAIN on the queries you design to ensure they are well optimized. 

It is also worth noting that if you are testing your query locally, that the way the query optimizes may differ than on live due to the amount of data and type of data on live vs local development.