Full-text indexes in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a full-text index on the 'content' column of the 'articles' table.

MySQL

CREATE FULLTEXT INDEX [1] ON articles(content);

Drag options to blanks, or click blank then click option'

Aidx_content

BPRIMARY

Ccontent_index

Dfulltext_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved keywords like PRIMARY as index name.

Leaving the index name blank.

Using invalid characters in the index name.

The index name can be any valid identifier. Here, idx_content is a common naming style for indexes.

2fill in blank

medium

Complete the query to search for the word 'database' using full-text search on the 'content' column in the 'articles' table.

MySQL

SELECT * FROM articles WHERE MATCH(content) AGAINST('[1]');

Drag options to blanks, or click blank then click option'

A'"database"'

B'database*'

C'database'

D'+database'

Attempts:

3 left

💡 Hint

Common Mistakes

Using double quotes instead of single quotes.

Adding operators like + without understanding their effect.

Leaving out quotes around the search term.

The simplest full-text search uses the word directly inside single quotes. Here, 'database' searches for the word.

3fill in blank

hard

Fix the error in the full-text search query to find rows containing the word 'mysql' in the 'description' column of 'products'.

MySQL

SELECT * FROM products WHERE MATCH(description) AGAINST([1] IN NATURAL LANGUAGE MODE);

Drag options to blanks, or click blank then click option'

A"mysql"

Bmysql

C'+mysql'

D'mysql'

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the search term.

Using double quotes instead of single quotes.

Adding operators when not needed.

The search term must be a string literal enclosed in single quotes. Without quotes, it causes a syntax error.

4fill in blank

hard

Fill both blanks to create a full-text index on columns 'title' and 'body' in the 'posts' table.

MySQL

CREATE FULLTEXT INDEX [1] ON posts([2]);

Drag options to blanks, or click blank then click option'

Aidx_title_body

Bposts_index

Ctitle, body

Dtitle body

Attempts:

3 left

💡 Hint

Common Mistakes

Using space instead of comma between column names.

Using invalid index names with spaces.

Omitting the column list.

The index name is idx_title_body. Multiple columns are listed separated by commas: title, body.

5fill in blank

hard

Fill all three blanks to write a full-text search query that finds rows in 'documents' where 'text' matches the phrase 'open source' using boolean mode.

MySQL

SELECT * FROM documents WHERE MATCH([1]) AGAINST([2] IN [3]);

Drag options to blanks, or click blank then click option'

Atext

B'"open source"'

CBOOLEAN MODE

DNATURAL LANGUAGE MODE

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting the phrase correctly.

Using NATURAL LANGUAGE MODE instead of BOOLEAN MODE for phrase search.

Using wrong column name.

The column is text. The search phrase must be quoted as '"open source"' to search exact phrase. The mode is BOOLEAN MODE to allow phrase search.

Practice

1. What is the main purpose of a FULLTEXT index in MySQL?

easy

A. To speed up searching for words inside text columns

B. To enforce uniqueness on text columns

C. To sort text columns alphabetically

D. To compress text data for storage

Solution

1. Step 1: Understand what FULLTEXT indexes do

   FULLTEXT indexes are designed to make searching for words inside large text columns faster and more efficient.

2. Step 2: Compare with other index types

   Unlike unique or sorting indexes, FULLTEXT indexes focus on text search, not uniqueness or ordering.

3. Final Answer:

   To speed up searching for words inside text columns -> Option A

4. Quick Check:

   FULLTEXT index = speeds up text search [OK]

Hint: FULLTEXT indexes are for fast word searches in text columns [OK]

Common Mistakes:

• Confusing FULLTEXT with UNIQUE index
• Thinking FULLTEXT sorts data
• Assuming FULLTEXT compresses data

2. Which of the following is the correct syntax to create a FULLTEXT index on the column description in a table products?

easy

A. CREATE INDEX idx_desc FULLTEXT ON products(description);

B. CREATE INDEX idx_desc ON products(description) FULLTEXT;

C. CREATE FULLTEXT INDEX idx_desc ON products(description);

D. CREATE FULLTEXT ON products(description) INDEX idx_desc;

Solution

1. Step 1: Recall correct FULLTEXT index syntax

   The correct syntax is: CREATE FULLTEXT INDEX index_name ON table_name(column_name);

2. Step 2: Match syntax with options

   CREATE FULLTEXT INDEX idx_desc ON products(description); matches the correct syntax exactly.

3. Final Answer:

   CREATE FULLTEXT INDEX idx_desc ON products(description); -> Option C

4. Quick Check:

   CREATE FULLTEXT INDEX ... ON ... [OK]

Hint: FULLTEXT index syntax: CREATE FULLTEXT INDEX name ON table(column) [OK]

Common Mistakes:

• Placing FULLTEXT keyword after INDEX
• Using wrong order of keywords
• Omitting INDEX keyword

3. Given a table articles with a FULLTEXT index on the content column, what will the following query return?

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

medium

A. Syntax error because MATCH() requires multiple columns

B. All article IDs sorted alphabetically by content

C. All article IDs where content exactly equals 'database'

D. All article IDs where the word 'database' appears in content

Solution

1. Step 1: Understand MATCH() AGAINST() usage

   This function searches the FULLTEXT indexed column for the given word or phrase.

2. Step 2: Interpret the query result

   The query returns IDs of rows where 'database' appears anywhere in the content column.

3. Final Answer:

   All article IDs where the word 'database' appears in content -> Option D

4. Quick Check:

   MATCH() AGAINST() = full-text search result [OK]

Hint: MATCH() AGAINST() finds rows containing the search word [OK]

Common Mistakes:

• Thinking it matches exact whole content
• Assuming it sorts results
• Believing MATCH() needs multiple columns

4. You wrote this query but it returns no results even though matching rows exist:

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

The body column has a FULLTEXT index. What is the likely problem?

medium

A. AGAINST() requires the IN BOOLEAN MODE modifier

B. The word 'the' is too common and ignored by default stopword list

C. FULLTEXT index must be created on multiple columns

D. MATCH() cannot be used on a single column

Solution

1. Step 1: Recall FULLTEXT stopword behavior

   MySQL ignores very common words (stopwords) by default in FULLTEXT searches.

2. Step 2: Understand why no results appear

   If 'the' is in the stopword list, the search ignores it, returning no matches.

3. Final Answer:

   The word 'the' is too common and ignored by default stopword list -> Option B

4. Quick Check:

   Stopwords cause no results for common words [OK]

Hint: Common words may be ignored by FULLTEXT stopword list [OK]

Common Mistakes:

• Thinking MATCH() needs multiple columns
• Assuming IN BOOLEAN MODE is always required
• Believing FULLTEXT index must cover multiple columns

5. You want to find rows in the reviews table where the comment column contains either 'fast' or 'reliable'. The comment column has a FULLTEXT index. Which query correctly finds these rows using boolean mode?

hard

A. SELECT * FROM reviews WHERE MATCH(comment) AGAINST('fast | reliable' IN BOOLEAN MODE);

B. SELECT * FROM reviews WHERE MATCH(comment) AGAINST('+fast +reliable' IN BOOLEAN MODE);

C. SELECT * FROM reviews WHERE MATCH(comment) AGAINST('fast reliable' IN BOOLEAN MODE);

D. SELECT * FROM reviews WHERE MATCH(comment) AGAINST('"fast reliable"' IN BOOLEAN MODE);

Solution

1. Step 1: Understand boolean mode operators

   In boolean mode, | means OR, + means AND, and quotes mean phrase search.

2. Step 2: Identify correct OR query

   To find rows containing either 'fast' OR 'reliable', use 'fast | reliable'.

3. Step 3: Check other options

   SELECT * FROM reviews WHERE MATCH(comment) AGAINST('fast reliable' IN BOOLEAN MODE); searches for both words but not as OR; SELECT * FROM reviews WHERE MATCH(comment) AGAINST('+fast +reliable' IN BOOLEAN MODE); requires both words; SELECT * FROM reviews WHERE MATCH(comment) AGAINST('"fast reliable"' IN BOOLEAN MODE); searches exact phrase.

4. Final Answer:

   SELECT * FROM reviews WHERE MATCH(comment) AGAINST('fast | reliable' IN BOOLEAN MODE); -> Option A

5. Quick Check:

   Boolean OR uses | operator [OK]

Hint: Use | for OR in boolean full-text searches [OK]

Common Mistakes:

• Using space for OR instead of |
• Using + which means AND
• Using quotes for phrase instead of OR