Full-text indexes in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Full-text search basic query output

Given a MySQL table articles with a full-text index on the content column, what is the output of this query?

SELECT id, MATCH(content) AGAINST('database') AS score FROM articles WHERE MATCH(content) AGAINST('database');

Assume the table has these rows:

• 1, 'Introduction to database'
• 2, 'Advanced SQL queries'
• 3, 'Database indexing techniques'

Which rows will be returned?

MySQL

SELECT id, MATCH(content) AGAINST('database') AS score FROM articles WHERE MATCH(content) AGAINST('database');

A Rows with id 1 and 3 only

B Rows with id 1, 2, and 3

C Row with id 2 only

D No rows returned

💡 Hint

Full-text search matches rows containing the search word in the indexed column.

Rows 1 and 3 contain the word 'database' in their content, so they match the full-text search. Row 2 does not contain 'database', so it is excluded.

🧠 Conceptual

intermediate

Understanding full-text index limitations

Which of the following is a limitation of MySQL full-text indexes in natural language mode?

A They automatically update indexes in real-time without delay

B They index all columns regardless of data type

C They support searching inside binary data

D They ignore words shorter than 4 characters by default

💡 Hint

Think about common default settings for full-text search.

By default, MySQL full-text indexes ignore words shorter than 4 characters, which can affect search results.

📝 Syntax

advanced

Correct syntax for creating a full-text index

Which option shows the correct syntax to create a full-text index on the description column of the products table in MySQL?

A ALTER TABLE products ADD FULLTEXT INDEX idx_desc (description);

B CREATE INDEX idx_desc FULLTEXT ON products(description);

C CREATE FULLTEXT INDEX idx_desc ON products(description);

D ALTER TABLE products CREATE FULLTEXT INDEX idx_desc ON description;

💡 Hint

Remember the syntax for adding indexes with ALTER TABLE.

The correct syntax to add a full-text index is using ALTER TABLE with ADD FULLTEXT INDEX specifying the index name and columns.

❓ optimization

advanced

Improving full-text search performance

You have a large MySQL table with a full-text index on the comments column. Which approach will most improve full-text search query performance?

A Remove the full-text index and use LIKE queries instead

B Increase the minimum word length to 10 characters

C Use BOOLEAN MODE in the MATCH AGAINST clause

D Disable the full-text index temporarily during searches

💡 Hint

BOOLEAN MODE allows more control over search terms and can speed up queries.

Using BOOLEAN MODE allows more efficient searching with operators and can improve performance compared to natural language mode.

🔧 Debug

expert

Why does this full-text search return no results?

You run this query:

SELECT * FROM posts WHERE MATCH(title) AGAINST('the');

But it returns no rows, even though some titles contain the word 'the'. Why?

A The column 'title' is not indexed with a full-text index

B The word 'the' is a stopword and ignored by full-text search

C The full-text index is corrupted and needs rebuilding

D The query syntax is invalid and causes no matches

💡 Hint

Common words are often excluded from full-text indexes.

Words like 'the' are stopwords and ignored by default in MySQL full-text searches, so no matches are found.