Working with Indexes in MySQL

Indexes in MySQL work like the indexes you’ll find in a library.

Imagine a public or a school library with an enormous number of books on multiple shelves. It could take you hours to find a specific book.

Unless you are using indexes that will group books by subject and alphabetically.

Side note: Usually, indexes in MySQL are used to make the program run faster. So, provided that you are interested in learning about another tool that does the same job, head right into our tutorial on the views in SQL. Also, we are going to be using the ‘employees’ database. You can download it here.

The Similarity

The index of a table functions like the index of a book.

Basically, data is taken from a column of the table and is stored in a certain order in a distinct place, called an index.

If your dataset contained about 100 rows, you would find the record you are looking for in a millisecond. But working with such datasets is a mirage. Your datasets will typically contain hundreds of thousands or even millions of records. Logically, the larger a database is, the slower the process of finding the record or records you need.

How to Create Indexes in MySQL?

So, for a large database, such as the “employees” database, we can use an index that will increase the speed of searches related to a table.

Let’s create one!

The Syntax

As usual, it is intuitive.

1. CREATE INDEX
2. index name
3. ON
4. table name
5. and a column name, or column names, attached in parentheses.

The parentheses serve us to indicate the column names on which our search will be based. It will be sped up, and the data will be filtered in a quicker way. Speaking technically, the idea is to choose columns, so your search will be optimized. These must be fields from your data table you will search frequently.

Side note: Imagine we must frequently sort the people in the “employees” table according to their hire date. Let’s run the following query:

1 2 3 4 5 6 7 8 9 10 11

SELECT                  *   FROM                  employees   WHERE                  hire_date > ‘2000-01-01’;

We can see how many people have been hired after the 1st of January 2000 after running it.

12! And it took the computer around 0.235 seconds to deliver the result. Naturally, this number could vary on different machines. But we can use the number obtained here as an example.

Writing the Code

Now, we will create an index with a name starting with “I”, standing for “index”. It will be “I_hire_date”.

So, the code will be:

1	CREATE INDEX i_hire_date ON employees(hire_date);

From this moment, if we try to execute the same SELECT statement we ran before, its output will be delivered quicker.

You can tell that this is true from the picture above.

What Are Composite Indexes in MySQL?

Another useful feature we have in MySQL is composite indexes. They are applied to multiple columns, not just a single one. The syntactical structure to abide by is the same. All a programmer needs to do is carefully pick the columns that would optimize his search. Then, he should type the chosen column names in the parentheses attached to the table name.

So, let’s repeat the exercise, this time using a composite index.

How to Create a Composite Index?

We will create an index referring to the same ‘employees’ table, searching data from two fields – first name and last name.

The way the SQL optimizer works is the following.

1. First, it will look up rows based on the data in the first designated column; just as if you were using a single-column index.
2. Then, based on the obtained intermediary output, it will proceed and search based on the second value.

Implementing the Index

Therefore, let’s SELECT all employees bearing the name “Georgi Facello”. We can achieve that by writing:

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT                  *   FROM                  employees   WHERE                  first_name = ‘Georgi’                                 AND last_name = ‘Facello’;

Again, as you can see in the picture above, it took us approximately 0.172 seconds to obtain the output.

How about we create the “I_composite” index on the fields “first name” and “last name” from the “employees” table.  The query should look like this:

1	CREATE INDEX i_composite ON employees(first_name, last_name);

Then, we can re-run the SELECT statement.

As shown in the picture above, it is quicker.

Other Types of Indexes in MySQL

Basically, these two examples showed how beneficial it is to use indexes.

However, please remember there are also other types of indexes in MySQL. Surprisingly or not, you might have worked with some!

Primary and Unique keys are indexes in MySQL. And it makes sense, when you think of it. They represent columns on which a person would typically base their search. In the “employees” database, a good example is the “emp_no” primary key from the “employees” table.

The reason is, this column represents unique values an analyst could take advantage of to extract distinct values from the data table.

How to Display the Indexes?

There are two alternative ways you can ask Workbench to display a list with the indexes in use.

We can open the ‘info’ section of the database we are working with and select the “Indexes” tab to see a list of all indexes in the database.

In the same way, we can check the indexes related to a specific table. For instance, let’s select the “Indexes” tab for the “employees” table. What we will see is a list of three elements: the primary key column and the two objects we created in this tutorial – “I_hire_date” and “I_composite”.

Another Way to Do it

Alternatively, we can simply type and run:

1. SHOW INDEX FROM
2. the table name of interest
3. FROM
4. the database the table belongs to
5. and a semi-colon.

The following code will provide the same result, shown differently – in the result-set box.

In our case, we have selected a default database, so the second “FROM employees” could be omitted from the query.

Why Use Indexes?

To conclude, we would like to add that SQL specialists are always aiming for a good balance between the improvement of speed search and the resources used for its execution. An index occupies memory space and could be redundant unless it can contribute to a quicker search.

Therefore, for small datasets, the costs of having an index might be higher than the benefits. However, for large databases, a well-optimized index can make a positive impact on the search process.