# Understanding Database Indexes

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699471489614/6082e4ae-b1df-4d20-8ac3-72d25f8f2d97.webp align="center")

### What is a database Index?

A database index is a special data structure that helps to speed up the process of searching for specific rows in a database table. It does this by organizing the data in the table according to the values in one or more columns. An index can be thought of as a table of contents in a book, which helps the reader to quickly find the desired chapter or page.

### How Does an Index Work?

If you have a database with more than a million entries and you need to find a specific record, the database engine will scan each row one by one until it finds a match for the record you are searching for, and then return the result. However, with so many records, this scanning process could take several minutes, causing the application to slow down and become almost unusable.

Now this is where the Database Index shines, with an index things work a little bit differently, when you create an index on a column the database sorts the values in that column and creates a data structure that associates each value in the indexed column with a pointer to the corresponding row in the actual table.

### How To Create a Database Index

We will be using a running example to buttress further the concept of Database Indexing. It is assumed that you have MYSQL installed on your machine.

First, we create a new Database with the following command.

```sql
CREATE DATABASE index_demo;
```

Then we create a table inside our brand-new database with the following command.

```sql
CREATE TABLE customer (
    cust_name VARCHAR(20) NOT NULL, 
    age INT, 
    email VARCHAR(20), 
    phone_no VARCHAR(20) );
```

Now we will use this code to populate the table we just created inside our database, you can add multiple records to the table.

```sql
INSERT INTO customer (name, age, email, phone_no)
VALUES ('Samuel', 30, 'samuel@gmail.com', '2347067322873'); 
```

Cool stuff!

Now we have some dummy data inside our database. Since our database is not indexed yet if we run a `SELECT` query using the `EXPLAIN` command, we get this feedback:

```sql
EXPLAIN SELECT * FROM customer where cust_name = 'Samuel';
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699368492398/3ed09784-15d2-4b76-86fa-2dc7cecc0b8a.png align="center")

`EXPLAIN` shows how the query engine plans to execute the query. In the above screenshot, you can see that the `rows` column returns `5` & `possible_keys` returns `null`. `possible_keys` represents what all available indices are there which can be used in this query. The `key` column represents which index is actually going to be used out of all possible indices in this query.

### PRIMARY KEYS

If you noticed when creating the `customer` table columns, we did not explicitly declare a Primary Key for our table, we will now make the `EMAIL` field our Primary Key, assuming that no 2 users in our database should have the same email address. Just before we do that, please bear in mind the following criteria when creating a Primary Key:

* A primary key should be part of many vital queries in your application.
    
* A primary key is a constraint that uniquely identifies each row in a table. If multiple columns are part of the primary key, that combination should be unique for each row.
    
* The primary key should be Non-null. Never make null-able fields your primary key. By ANSI SQL standards, primary keys should be comparable to each other, and you should definitely be able to tell whether the primary key column value for a particular row is greater, smaller or equal to the same from other row. Since `NULL` means an undefined value in SQL standards, you can’t deterministically compare `NULL` with any other value, so logically `NULL` is not allowed.
    
* The ideal primary key type should be a number like `INT` or `BIGINT` because integer comparisons are faster, so traversing through the index will be very fast.
    

Often we define an `id` field as `AUTO INCREMENT` in tables & use that as a primary key, but the choice of a primary key depends on developers.

### **What if you don’t create any primary key yourself?**

For this tutorial, we used MYSQL with the default InnoDB database engine, if you did not explicitly declare a Primary Key, innoDB by default implicitly creates one for you because InnoDB by design must have a primary key in every table. So once you create a primary key later on for that table, InnoDB deletes the previously auto defined primary key.

Since we don’t have any primary key defined as of now, let’s see what InnoDB by default created for us, run the command:

```sql
SHOW EXTENDED INDEX FROM customer;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699372296156/541c35c2-2ffd-44ff-a1ea-a3af7585a576.png align="center")

`EXTENDED` shows all the indices that are not usable by the user but managed completely by MySQL.

Here we see that MySQL has defined a composite index on `DB_ROW_ID` , `DB_TRX_ID`, `DB_ROLL_PTR`, and all columns defined in the table. In the absence of a user defined primary key, this index is used to find records uniquely.

Let’s now create the primary index on `email` and examine the created index:

```sql
ALTER TABLE customer ADD PRIMARY KEY (email)
SHOW INDEXES FROM customer;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699453059655/6c959eda-c37e-4f20-8ed7-5cbf047dec9c.png align="center")

In the above screenshot, we see that one primary index is created on the column `email`. The columns of the image are described as follows:

`Table` : The table on which the index is created.

`Non_unique`: If the value is 1, the index is not unique, if the value is 0, the index is unique. From our example, the email column is unique hence we are seeing a 1 there.

`Key_name`: The name of the index created. The name of the primary index is always `PRIMARY` in MySQL, irrespective of if you have provided any index name or not while creating the index.

`Seq_in_index`: The sequence number of the column in the index. If multiple columns are part of the index, the sequence number will be assigned based on how the columns were ordered during the index creation time. The sequence number starts from 1.

`Collation`: how the column is sorted in the index. `A` means ascending, `D` means descending, `NULL` means not sorted.

`Cardinality`: The estimated number of unique values in the index. More cardinality means higher chance that the query optimizer will pick the index for queries.

`Sub_part` : The index prefix. It is `NULL` if the entire column is indexed. Otherwise, it shows the number of indexed bytes in case the column is partially indexed.

`Packed`: Indicates how the key is packed; `NULL` if it is not.

`Null`: `YES` if the column may contain `NULL` values and blank if it does not.

`Index_type`: Indicates which indexing data structure is used for this index. Some possible candidates are — `BTREE`, `HASH`, `RTREE`, or `FULLTEXT`.

`Comment`: The information about the index not described in its own column.

`Index_comment`: The comment for the index specified when you created the index with the `COMMENT` attribute.

Now let’s see if this index reduces the number of rows which will be searched for a given `email` in the `WHERE` clause of a query. Remember before creating an index, the database had to query all the rows in the table to find a match for the resource we were looking for, let us see if there is any improvement on that.

```sql
EXPLAIN SELECT * FROM customer WHERE email = 'samuel@gmail.com';
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699453686300/0ade1b4f-c122-409f-bcfb-e96d5ef88b52.png align="center")

In this snapshot, notice that the `rows` column has returned `1` only, the `possible_keys` & `key` both returns `PRIMARY` . So it essentially means that using the primary index named as `PRIMARY` (the name is auto assigned when you create the primary key), the query optimizer just goes directly to the record & fetches it. It’s very efficient. This is exactly what an index is for — to minimize the search scope at the cost of extra space.

### Using the Create Index Command to Create Database Index

You will notice that we did not explicitly create an index on the email column, we just made the email column a Primary Key and mysql automatically created an index on that column. Often times when building applications you might need to explicitly create an index, this can be done using the `create index` command.

```sql
CREATE INDEX index_demo
ON customer (email);
```

This creates an index on the email column on the customer table.

### When Should You Use a Database Index?

Indexes are most useful when a database is performing more *read* operations than *write* operations. Let’s pretend we are building a site like [IMDB](https://www.imdb.com/). Our database has many tables, including a `movies` table. Our `movies` table has the columns: `title`, `genre`, `rating`, and `release_date`. While IMDB adds (or *writes*) movies to their `movies` table quite frequently, I’m willing to bet the number of times a user performs a search on the website to query `movies` is far greater.

Since data retrieval operations on our `movies` table are the most common action, we will be well served to add indexes to the most commonly queried columns. Let’s add an index to the `title` column since this is the most common lookup in the `movies` table. We will sort the newly created index alphabetically. So our new index is essentially a copy of the `title` column in alphabetical order with pointers to the original rows of each unordered title in the table. Now, when a user searches for a movie by its title, it can be found much faster since the database has a better idea of where to search for it.

### **Downsides of database indexes**

Let's go over some of the possible downsides of using too many database secondary indexes.

### **Additional storage**

The first and perhaps most obvious drawback of adding indexes is that they take up additional storage space. The exact amount of space depends on the size of the table and the number of columns in the index, but it's usually a small percentage of the total size of the table. A basic index only needs to store the values of the indexed columns as well as a pointer to the row in the table. So for a column that contains integers, the index will only need to store the integer values. This space will increase if the column contains strings because the index will need to store the string values as well as the length of each string.

This is important to consider if you have large datasets, as adding multiple indexes to a table can quickly use a significant amount of additional storage space.

### **Slower writes**

When you add an index, it has to be updated whenever a row is inserted, updated, or deleted. This means that writes will be slower. Before you add an index, you should consider whether you will be doing a lot of writes to the table and whether or not you can afford to slow down the writes.