Lesson 27 of the SQL Optimization Course: Monitoring MySQL Full-Text Indexes

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

Parameter Configuration for Full-Text Monitoring

MySQL provides configurable parameters to monitor full-text indexes:

innodb_ft_aux_table: Specifies the target table for monitoring (format: database/table).
innodb_ft_cache_size: Caches INSERT/UPDATE data before flushing to disk (default 8MB, range 1.6MB–80MB).
innodb_ft_total_cache_size: Instance-wide cache limit (default 640MB, range 32MB–1.6GB).
innodb_ft_result_cache_limit: Sets result set cache threshold (default 2GB).
innodb_ft_enable_diag_print: Enables diagnostic logging for troubleshooting.

Example Configuration:

1
2
SET GLOBAL innodb_ft_aux_table = 'mydb/my_ft_table';
SET GLOBAL innodb_ft_cache_size = 16000000; -- 16MB

Metadata Tables for Full-Text Indexing

MySQL uses specialized system tables to track full-text operations:

Table Name	Purpose
`INNODB_FT_CONFIG`	Stores configuration metadata (e.g., `synced_doc_id`).
`INNODB_FT_INDEX_TABLE`	Contains inverted index data.
`INNODB_FT_INDEX_CACHE`	Buffers newly inserted keywords before merging with disk tables.
`INNODB_FT_DELETED`	Tracks deleted documents pending cleanup via `OPTIMIZE TABLE`.
`INNODB_FT_BEING_DELETED`	Snapshot of `INNODB_FT_DELETED` during optimization.

Key Insight:

1
2
-- View current sync status
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;

Practical Monitoring Demonstration

Step 1: Create Test Table

1
2
3
4
5
CREATE TABLE ft_monitor (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT,
  FULLTEXT ft_content (content)
) ENGINE=InnoDB;

Step 2: Insert Sample Data

1
2
3
4
5
INSERT INTO ft_monitor (content) 
VALUES 
  ('MySQL full-text indexing'),
  ('performance optimization techniques'),
  ('database monitoring tools');

Step 3: Analyze Index Cache

1
2
3
4
-- Check cached keywords
SELECT WORD, DOC_COUNT 
FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 
WHERE DOC_ID > 0;

Step 4: Trigger Index Merge

1
2
3
4
-- Force index merge with OPTIMIZE TABLE
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE ft_monitor;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

Step 5: Observe Deletion Tracking

1
2
DELETE FROM ft_monitor WHERE id = 2;
SELECT DOC_ID FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.