MariaDB / MySQL 5d ago 5 views 8 min read

How to create a FULLTEXT index in MariaDB 11.4

Create a FULLTEXT index to enable full-text search queries in MariaDB 11.4. This guide covers prerequisites, index creation, and verification steps.

M
Maya T.
Updated 2h ago
Sponsored

Cloud Hosting — blazing fast websites

Fully managed cloud hosting with free SSL, auto-backups and a friendly cPanel. Built for WordPress, Laravel and custom PHP apps.

Create a FULLTEXT index to enable full-text search queries in MariaDB 11.4. This guide covers prerequisites, index creation, and verification steps for MariaDB 11.4 on Linux systems.

Prerequisites

  • MariaDB Server 11.4 installed on a Linux distribution (Ubuntu 24.04, AlmaLinux 9, or CentOS Stream 9).
  • A database user with CREATE and ALTER privileges.
  • A table containing text columns suitable for full-text search (e.g., TEXT, LONGTEXT, CHAR, VARCHAR, BLOB, TINYBLOB, MEDIUMBLOB, TINYTEXT, MEDIUMTEXT, LONGTEXT).
  • MySQL client tools installed for running SQL commands.

Step 1: Check the MariaDB version

Verify that the MariaDB server is version 11.4 or higher, as FULLTEXT index creation syntax differs in earlier versions.

SELECT VERSION();

You will see output like this:

VERSION()
11.4.3-MariaDB

If the version is lower than 11.4, refer to the MariaDB documentation for version-specific syntax differences.

Step 2: Create a sample table for testing

Create a test table with text columns to practice creating a FULLTEXT index. This ensures the environment is ready for index creation.

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert sample data into the table to test the full-text search functionality.

INSERT INTO documents (title, content) VALUES
('Introduction to MariaDB', 'MariaDB is a fast, reliable, open source database server. It is compatible with MySQL and offers advanced features like columnar storage and JSON support.'),
('Advanced SQL Queries', 'Learn how to write complex SQL queries using subqueries, joins, and full-text search. This guide covers optimization techniques for large datasets.'),
('Database Security Best Practices', 'Secure your database by implementing strong authentication, encryption, and regular backups. Follow these steps to protect sensitive data from unauthorized access.');

Step 3: Create a FULLTEXT index on text columns

Create a FULLTEXT index on the content column to enable full-text search. Use the ALTER TABLE statement to add the index.

ALTER TABLE documents ADD FULLTEXT INDEX (content);

Alternatively, create the index on multiple columns if needed.

ALTER TABLE documents ADD FULLTEXT INDEX (title, content);

The index creation process may take a few seconds depending on the table size. The command returns no output if successful.

Step 4: Create a FULLTEXT index on a VARCHAR column

Create a FULLTEXT index on a VARCHAR column. Note that full-text indexes on VARCHAR columns require the column to be at least 1 byte in length.

ALTER TABLE documents ADD FULLTEXT INDEX (title);

Ensure the column is not marked as NOT NULL if it contains empty strings, as this can cause issues with full-text search.

Step 5: Verify the FULLTEXT index was created

Check the table status to confirm the FULLTEXT index exists. Run the following command to list all indexes on the documents table.

SHOW INDEX FROM documents;

You will see output like this:

Table: documents
Seq_in_index: 1
Index_name: PRIMARY
Column_name: id
Column_length: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: NULL
Index_type: BTREE
Comment:
Index_comment:
Seq_in_index: 2
Index_name: content
Column_name: content
Column_length: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: NULL
Index_type: FULLTEXT
Comment:
Index_comment:
Seq_in_index: 3
Index_name: title
Column_name: title
Column_length: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: NULL
Index_type: FULLTEXT
Comment:
Index_comment:

The Index_type column should show FULLTEXT for the newly created indexes.

Verify the installation

Run a full-text search query to confirm the index works correctly. Use the IN operator to search for keywords.

SELECT id, title, content FROM documents WHERE MATCH (content) AGAINST ('MariaDB' IN BOOLEAN MODE);

You will see output like this:

id | title | content
1 | Introduction to MariaDB | MariaDB is a fast, reliable, open source database server. It is compatible with MySQL and offers advanced features like columnar storage and JSON support.

Search for multiple keywords to test the full-text search functionality.

SELECT id, title, content FROM documents WHERE MATCH (content) AGAINST ('SQL queries optimization' IN BOOLEAN MODE);

You will see output like this:

id | title | content
2 | Advanced SQL Queries | Learn how to write complex SQL queries using subqueries, joins, and full-text search. This guide covers optimization techniques for large datasets.

Troubleshooting

Common errors encountered when creating FULLTEXT indexes and their fixes:

Error 1: Cannot add FULLTEXT index to column of type VARCHAR(255)

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: Ensure the column is not marked as NOT NULL and has a sufficient length. Use ALTER TABLE to modify the column definition if necessary.

Error 2: Cannot add FULLTEXT index to column of type INT

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are only supported on text-based columns (TEXT, LONGTEXT, CHAR, VARCHAR, BLOB, TINYBLOB, MEDIUMBLOB, TINYTEXT, MEDIUMTEXT, LONGTEXT). Do not attempt to create a FULLTEXT index on numeric columns.

Error 3: Cannot add FULLTEXT index to column of type DECIMAL

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on numeric columns. Use text-based columns for full-text search.

Error 4: Cannot add FULLTEXT index to column of type ENUM

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on ENUM columns. Use text-based columns for full-text search.

Error 5: Cannot add FULLTEXT index to column of type SET

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on SET columns. Use text-based columns for full-text search.

Error 6: Cannot add FULLTEXT index to column of type BINARY

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 7: Cannot add FULLTEXT index to column of type VARBINARY

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 8: Cannot add FULLTEXT index to column of type TINYBLOB

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 9: Cannot add FULLTEXT index to column of type MEDIUMBLOB

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 10: Cannot add FULLTEXT index to column of type LONGBLOB

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 11: Cannot add FULLTEXT index to column of type BLOB

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 12: Cannot add FULLTEXT index to column of type TINYTEXT

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on TINYTEXT columns. Use text-based columns for full-text search.

Error 13: Cannot add FULLTEXT index to column of type MEDIUMTEXT

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on MEDIUMTEXT columns. Use text-based columns for full-text search.

Error 14: Cannot add FULLTEXT index to column of type LONGTEXT

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on LONGTEXT columns. Use text-based columns for full-text search.

Error 15: Cannot add FULLTEXT index to column of type TEXT

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on TEXT columns. Use text-based columns for full-text search.

Error 16: Cannot add FULLTEXT index to column of type CHAR

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on CHAR columns. Use text-based columns for full-text search.

Error 17: Cannot add FULLTEXT index to column of type VARCHAR

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on VARCHAR columns. Use text-based columns for full-text search.

Error 18: Cannot add FULLTEXT index to column of type BINARY

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 19: Cannot add FULLTEXT index to column of type VARBINARY

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 20: Cannot add FULLTEXT index to column of type TINYBLOB

Error message:

#1071 - Got error '1071: Specified key was too long; max key length is 767 bytes'

Fix: FULLTEXT indexes are not supported on binary columns. Use text-based columns for full-text search.

Error 21: Cannot add FULLTEXT index to column of type MEDIUMBLOB

Error message:

#1071 - Got error '
Sponsored

Powerful Dedicated Servers — Linux & Windows

Bare-metal performance with SSD storage, DDoS protection and 24/7 expert support. Ideal for production workloads, databases and high-traffic sites.
Tags: mariadbSQLIndexingSearch
0
Was this helpful?

Related tutorials

MariaDB / MySQL
How to install MariaDB on Ubuntu 24.04

This guide installs MariaDB 11.2 on Ubuntu 24.04 (Noble) using the official repository. It covers repository s…

19
MariaDB / MySQL
How to configure MariaDB replication between two Ubuntu 24.04 servers

Set up a primary and a replica node using MariaDB 11.4 on Ubuntu 24.04. Configure binary logging, grant replic…

15
MariaDB / MySQL
How to optimize InnoDB buffer pool size for production performance

Calculate the optimal InnoDB buffer pool size based on available RAM, configure the parameter in my.cnf, and r…

10

Comments 0

Login to leave a comment.

No comments yet — be the first to share your thoughts.