Creating indexes in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple index on the column 'name' in the 'employees' table.

MySQL

CREATE INDEX [1] ON employees(name);

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

Aemployee_index

Bidx_name

Cindex_name

Dname_index

Attempts:

3 left

💡 Hint

Common Mistakes

Using a reserved keyword as the index name.

Leaving out the index name entirely.

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

2fill in blank

medium

Complete the code to create a unique index on the 'email' column in the 'users' table.

MySQL

CREATE [1] INDEX idx_email ON users(email);

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

APRIMARY

BSPATIAL

CUNIQUE

DFULLTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY instead of UNIQUE for a non-primary key column.

Omitting the UNIQUE keyword when uniqueness is required.

The UNIQUE keyword ensures that all values in the 'email' column are unique.

3fill in blank

hard

Fix the error in the code to create an index on the 'created_at' column in the 'orders' table.

MySQL

CREATE INDEX idx_created_at ON orders([1]);

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

Acreated_at ASC

BcreatedAt

Ccreated_at DESC

Dcreated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Using camelCase instead of snake_case for column names.

Adding sorting keywords inside the parentheses.

The column name must match exactly as defined in the table. Here, created_at is the correct column name.

4fill in blank

hard

Fill both blanks to create a composite index on 'last_name' and 'first_name' columns in the 'contacts' table.

MySQL

CREATE INDEX [1] ON contacts([2], first_name);

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

Aidx_last_first

Blast_name

Clast

Didx_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a generic index name that doesn't reflect the columns.

Swapping the order of columns in the index.

The index name idx_last_first clearly indicates the columns indexed. The first column in the index must be last_name.

5fill in blank

hard

Fill all three blanks to create a fulltext index named 'idx_content' on the 'title' and 'body' columns in the 'articles' table.

MySQL

CREATE [1] INDEX [2] ON articles([3], body);

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

AFULLTEXT

Bidx_content

Ctitle

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE instead of FULLTEXT for text search.

Incorrect column names or order.

The FULLTEXT keyword creates a full-text index. The index is named idx_content and covers the columns title and body.