Adding indexes to TokuDB tables

TokuDB has advanced indexing capabilities compared to other storage engines, but we can't use them if we don't add them to our tables.

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

Follow the ensuing steps:

1. Launch the mysql command-line client and connect to the isfdb database.

2. View the current indexes on the authors table with the following command:

   SHOW INDEXES FROM authors; 
   

3. Alter the authors table to use the TokuDB storage engine and change the index on the author_canonical column to CLUSTERING, as shown in the following commands:

   ALTER TABLE authors
     DROP KEY canonical,
     ADD CLUSTERING KEY canonical (author_canonical(50)),
     ENGINE=TokuDB;
   

4. Create another index, this time on the author_birthdate column, as shown in the following commands:

   CREATE CLUSTERING INDEX birthdate
     ON authors (author_birthdate);
   

5. View the indexes again as we did in...