Full-text indexes in MySQL - Mini Project: Build & Apply

Creating and Using Full-text Indexes in MySQL

📖 Scenario: You are building a simple article search feature for a blog website. The blog stores articles with titles and content. To help users find articles by keywords quickly, you want to use MySQL's full-text search capability.

🎯 Goal: Build a MySQL table for articles, add a full-text index on the title and content columns, and write a query to search articles by keywords using the full-text index.

📋 What You'll Learn

Create a table named articles with columns id, title, and content

Add a full-text index on the title and content columns

Insert sample articles with exact titles and content

Write a query using MATCH(title, content) AGAINST() to find articles matching a keyword

💡 Why This Matters

🌍 Real World

Full-text indexes are used in blogs, news sites, and e-commerce to quickly find relevant text content based on user search keywords.

💼 Career

Knowing how to create and use full-text indexes is important for database developers and backend engineers to optimize search features in applications.

1

Create the articles table

Create a table called articles with these columns: id as an integer primary key that auto-increments, title as a VARCHAR(100), and content as TEXT.

MySQL

-- Your code here to create the articles table

Need a hint?

Use CREATE TABLE with id INT AUTO_INCREMENT PRIMARY KEY and the specified column types.

2

Add a full-text index on title and content

Add a full-text index named ft_index on the title and content columns of the articles table.

MySQL

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  content TEXT
);
-- Your code here to add the full-text index

Need a hint?

Add FULLTEXT KEY ft_index (title, content) inside the table definition.

3

Insert sample articles

Insert these exact rows into the articles table: (1, 'MySQL Tutorial', 'Learn how to use MySQL databases.'), (2, 'Full-text Search', 'Full-text indexes help search text quickly.'), and (3, 'Database Basics', 'Introduction to databases and SQL.').

MySQL

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  content TEXT,
  FULLTEXT KEY ft_index (title, content)
);
-- Your code here to insert sample articles

Need a hint?

Use INSERT INTO articles (id, title, content) VALUES with the exact rows.

4

Write a full-text search query

Write a SELECT query to find all articles where the title or content matches the keyword 'search' using the full-text index with MATCH(title, content) AGAINST('search').

MySQL

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  content TEXT,
  FULLTEXT KEY ft_index (title, content)
);

INSERT INTO articles (id, title, content) VALUES
(1, 'MySQL Tutorial', 'Learn how to use MySQL databases.'),
(2, 'Full-text Search', 'Full-text indexes help search text quickly.'),
(3, 'Database Basics', 'Introduction to databases and SQL.');

-- Your code here to write the full-text search query

Need a hint?

Use SELECT * FROM articles WHERE MATCH(title, content) AGAINST('search') to find matching articles.