Chapter 3. Indexing your data for High-Performing Queries

In the previous chapter, you learned to apply queries on your data stored in the MySQL database. You learned the different syntax of the select query, how to join tables, and how to apply aggregate functions on the table.

In this chapter, you will learn below topics on what is indexing and different types of indexes:

• MySQL indexing
• MySQL index types
• Indexing JSON data

Let's assume that we have a database table that has more then 50 lakh records of email addresses and you want to fetch one record out of this table. Now, if you write a query to fetch an email address, MySQL will have to check in each and every row for the values matching your queried email address. If MySQL takes one microsecond to scan one record, then it will take around five seconds to load just one record and, as the number of records increases in a table, the time taken will also increase exponentially, which would affect performance!

Fortunately, current relational...

MySQL indexing

MySQL supports various indexes on its tables for the faster access of records. Before we define indexes on the tables, it is important to know when we need to index data, and it's also equally important to select the proper field to create indexes.

The following list shows when to use indexing:

• When grouping based on a specific column is required
• When sorting on a specific column is required
• When you need to find the minimum and maximum values from the table
• When we have a large dataset and need to find a few records based on certain conditions frequently
• When you need to fire a query that has a join between two or more tables

Index structures

There are different structures used by various indexing methods to store the index information in the database:

• Bitmap indexes
• Sparse indexes
• Dense indexes
• B-Tree indexes
• Hash indexes

Let's quickly go through each of these indexes.

Bitmap indexes

As the name suggests, a Bitmap index stores column information as a bit. Bitmap indexing is used when there...

MySQL 8 index types

In the previous topics, you learned different index structures and how to create indexes on a table. Now let's see different indexes in detail that are available in MySQL and their importance.

When you create a table in MySQL, there are five types of index options available:

• PRIMARY
• UNIQUE
• COLUMN
• FULLTEXT
• SPATIAL

You can choose any of these indexes on your table based on your database design; the frequency of data and columns used in the query would accordingly help you define where indexes need to be applied.

Defining a primary index

The primary key is used to identify each row uniquely. The column on which the primary key is defined is unique in nature and contains not null values. In the following section, you will learn how to use the primary key and the difference between a surrogate key and natural key.

Primary indexes

Every InnoDB table contains one special index to identify each row uniquely. This column is also referred to as a clustered index. A clustered index is synonymous...

Indexing JSON data

Big data consists of many documents that are stored in the JSON format, so it is necessary to have proper indexing on JSON data. There is no direct way to define an index on JSON data. We can always create columns from the JSON data and then leverage the generated columns to support indexes.

Let's first understand what generated columns are in MySQL.

Generated columns

Generated columns generally do not have actual information, but they store the information gathered from other columns of the table using some expressions or calculations.

Generated columns can be divided into two types: virtual generated columns and stored generated columns.

Virtual generated columns

Columns defined as virtual generated columns do not actually store the value but they are calculated on the fly. If there is no column type defined for generated columns, it would use virtual columns by default. InnoDB supports secondary indexes on virtually generated columns. As the value of virtual columns are calculated...

Summary

You now have good knowledge of the different types of index structures supported by MySQL and now how to create an index in MySQL. You also learned different types of indexes available in MySQL 8 such as clustered index, covering index, descending index, and invisible index. We got a good understanding of fulltext indexing and how natural language fulltext indexes and fulltext indexes on Boolean mode works.

In the next chapter, we will cover some interesting things to boost MySQL performance such as how to set up Memcache, usage of Memcache, Memcache configuration, using Memcache APIs, how to analyze data stored in Memcache, and how to use Memcache APIs in MySQL 8.