Indexing

Introduction

The use of indexes is a way to utilize a database with better performance.

By using an index, you can retrieve results faster.

This is especially useful with large datasets.

---

1. Example of indexing

We will look at how indexing works using an example.

---

1.1. Table scores

We will look at a table with scores of students for tests:

score_id	score	student_id
1	5.2	1
2	8.0	1
3	7.6	2
4	3	1
5	8.2	3
6	8.9	3
7	5.5	2

In this table, we see 3 columns:

• score_id: A unique id.
• score: The score for a test.
• student_id: The id of a student.

---

1.2. Problem statement

Suppose we want to execute the following query:

SELECT
  *
FROM
  scores
WHERE
  student_id = 2;

In that case, the query checks for each row if a value in column student_id is equal to 2.

If there are many records in the table, this can take a long time.

---

1.3. Solution indexing

If we were to apply an index on column student_id, something like the following would happen:

score_id	score	student_id
1	5.2	1
2	8.0	1
4	3	1
3	7.6	2
7	5.5	2
5	8.2	3
6	8.9	3

The index brings order.

This makes it easier and therefore faster to find which rows have a certain student_id.

This is a simplified representation. In reality, the table is not re-sorted.

---

2. Create index syntax

You can create a column (or columns) in a table as an index as follows:

CREATE INDEX <index_name>
ON <table_name> (<column_1>, <column_2>, ...);

For example, for the column student_id in table students:

CREATE INDEX idx_student_id
ON students (student_id);

---

Summary

• An index ensures faster search results.
  • Better performance of a query.
• There are various options, we have looked at a first example here.