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.