MySQL Indexes Demystified

A lot of developers concentrate on code optimization when developing apps and completely ignore the database (except to pass queries to it).

Meanwhile, a large percentage of bottlenecks can be attributed to the database back-end that powers the app/website.

Some of these bottlenecks are traceable to database queries. Most website content is stored inside of a database, e.g MySQL (which happens to be the most used database on the internet).

This means each page view most likely results in a database query or even multiple queries.

As records increase in a table, it takes longer to retrieve information from that table. It gets worse if you have to retrieve data from multiple joined tables.

Indexes were created for just this scenario. So what is a database index?

What is a database index

Simply put, a database index increases the speed of data retrieval from a table. Without an index, a query will need to go through each record in order to get to the record(s) that meet your search criteria. This can take an awfully long time if your database table runs into millions of records.

So how does an index solve this?

To understand a database index, we will look at one index that's been around long before electronic databases.

Imagine searching for information in a book. You have to flip through every page until you come to the page that contains the information you need.

The larger the number of pages in the book is, the longer it will take to get to the information. This is what it looks like running database queries on tables without an index.

In fact, in database parlance, it's called a table scan and it's bad for performance.

Now, imagine that instead of flipping through every page in the book, you simply turn to the index at the back of the book, locate the word in the alphabetical listing, get the page number, and flip straight to the page.

That is several orders of magnitudes faster that going through every page.

A database index operates in the same manner. The table column data is stored in an order in a separate structure called an index. A query consults the index and is directed to the exact location of the record(s) in the table.

Even though indexes will generally perform, using the appropriate type of index can aid performance even better.

However, there are different types of indexes and we will explain each type below.

Imagine a table of staff with over 100,000 rows with the structure below:

first_name
last_name
phone_number
date_of_birth
salary
department
**Column Index
**

Imagine the following query:

select * from staff where last_name like 'smit%'

Without an index, this query will do a table scan, i.e check through every record in the table and select the ones that match the search criteria. As we said before, this is very bad for query performance.

So what do we do? We will create an index on the
last_name column like below. This is syntax for a MySQL database index creation.

CREATE INDEX idx_last_name ON staff ('last_name');

This creates a column index on the last_name column, which will speed up the data retrieval significantly.

Unique Index
A unique index enforces a unique constraint on the column(s) upon which it is defined. This means that this column(s) will not accept duplicate values, or in the case of a multi-column unique index, it will not accept a duplicate of the exact same values combination.

A unique index could be set on a column, such phone_number, in the table defined in the beginning of this article.

A unique index looks exactly like a primary key. However, a table can only have one primary key defined, but may have several unique indices on several columns.

Note that NULL is treated as a unique value.

Composite Index
This is a single index that combines one or more columns (indexes on multiples columns). Suppose we run the below query:

select * from staff where last_name like 'smit%' order by date_of_birth

The above query would run better if we have an a combined index on the two columns last_name and date_of_birth.

Create a combined index as shown below:

CREATE INDEX idx_last_name_and_date_of_birth ON staff (‘last_name’, date_of_birth);

Conclusion
Once you notice that your data retrieval queries are taking too long, create indexes on the columns that you search on.
e.g

CREATE INDEX idx_last_name ON staff ('last_name');