Creating an index

An index helps MariaDB (or any database, really) to quickly locate often looked-for data that it will otherwise have to search for by reading through our tables row by row. Creating indexes of often-queried columns in large tables is a basic, but very useful optimization.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Create an index on the email_address column of the emails table:

   CREATE INDEX email ON emails(email_address(50));
   

3. Show the indexes on the emails table with the following command:

   SHOW INDEX FROM emails\G
   

4. The output will look similar to the following screenshot:

   

How it works...

The emails table already has an index, the primary key. This is the most common type of index, but if we rarely search in a large table for a record matching a primary key...