Full-text indexes in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a full-text index in MySQL?

A full-text index is a special type of index in MySQL that allows fast searching of text columns for words or phrases, enabling efficient text-based searches.

beginner

Which MySQL storage engines support full-text indexes?

MyISAM and InnoDB storage engines support full-text indexes in MySQL.

beginner

How do you create a full-text index on a column named 'content' in a table 'articles'?

You use the SQL command: <br>CREATE FULLTEXT INDEX idx_content ON articles(content);

beginner

What SQL clause is used to perform a full-text search in MySQL?

The MATCH() AGAINST() clause is used to perform full-text searches in MySQL.

intermediate

What is the difference between natural language mode and boolean mode in full-text searches?

Natural language mode searches for relevant words without operators, ranking results by relevance. Boolean mode allows use of operators like +, -, * to refine search conditions.

Which MySQL command creates a full-text index on the 'description' column of the 'products' table?

A CREATE FULLTEXT INDEX idx_desc ON products(description);

B CREATE INDEX idx_desc ON products(description);

C CREATE TEXT INDEX idx_desc ON products(description);

D CREATE FULLTEXT KEY idx_desc ON products(description);

The correct syntax to create a full-text index is CREATE FULLTEXT INDEX followed by the index name and the table and column.

Which storage engine in MySQL supports full-text indexes by default?

A MEMORY

B InnoDB

C CSV

D ARCHIVE

InnoDB supports full-text indexes starting from MySQL 5.6 and later.

What does the MATCH() function do in a full-text search?

A Filters rows by exact match

B Defines the index to use

C Creates a new full-text index

D Specifies the columns to search in

MATCH() specifies which columns to search for the given text in a full-text search.

Which mode allows use of operators like + and - in full-text searches?

A Boolean mode

B Natural language mode

C Query expansion mode

D Exact match mode

Boolean mode allows operators to refine search queries.

What is a limitation of full-text indexes in MySQL?

A They only work on numeric columns

B They cannot be used on InnoDB tables

C They require text-based columns like CHAR, VARCHAR, or TEXT

D They automatically index all columns

Full-text indexes work only on text-based columns such as CHAR, VARCHAR, or TEXT.

Explain how to create and use a full-text index in MySQL for searching text data.

Think about the steps from creating the index to querying with it.

Describe the benefits and limitations of using full-text indexes in MySQL.

Consider both what full-text indexes help with and what to watch out for.