Creating a full-text index

A full-text index is a special type of index optimized to search through the text-based columns. They can only be created for columns of the type CHAR, VARCHAR, and TEXT.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from 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 a FULLTEXT index on the note_note column of the notes table using the following command:

   CREATE FULLTEXT INDEX note ON notes(note_note);
   

3. When MariaDB has finished creating the index, we will get an output similar to the following:

   Query OK, 246719 rows affected (11.08 sec) 
   Records: 246719  Duplicates: 0  Warnings: 0 
   

4. Show the indexes on the notes table with the following command:

   SHOW INDEX FROM notes\G
   

5. The output of the SHOW command will look like the following screenshot:

   

How it works...

A FULLTEXT index enables us to search...