Working with Indexes in MySQL

SQL Tutorials 9 min read
MySQL Indexes
Blog / SQL Tutorials / 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.

library with book highlighted, indexes in mySQL

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.

sql views

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!

data science training

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.

create index

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.

these must be fields from your data table you will search frequently, indexes in mysql

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

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 rows returned, indexes in mysql

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:

CREATE INDEX i_hire_date ON employees(hire_date);

index with a name starting with “I”, indexes in mysql

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

0.094 sec, indexes in mysql

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.

create index, indexes in mysql

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.

first name last name, indexes in mysql

Implementing the Index

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

SELECT

               *

FROM

               employees

WHERE

               first_name = ‘Georgi’

                              AND last_name = ‘Facello’;

0.172 sec, indexes in mysql

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:

CREATE INDEX i_composite ON employees(first_name, last_name);

Then, we can re-run the SELECT statement.

0.032 sec, indexes in mysql

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.

emp_no INT, indexes in mysql

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.

employees

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”.

indexes in tables

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.

from employees

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.

small datasets large datasets

The Next Step

So, if you can’t wait to dive into another advanced SQL topic, feel free to embark on the journey where we will talk about the CASE statement in SQL.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: The CASE Statement

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

free SQL cheat sheet

You have Successfully Subscribed!

×
EXTENDED SALE
Learn Data Science this Summer!
Get 50% OFF