Index maintenance in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

CREATE INDEX [1] ON users (email);

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

Aindex_email

Bidx_email

Cemail_index

Dusers_email_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved words as index names

Leaving out the index name

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

2fill in blank

medium

Complete the code to drop the index named 'idx_email' from the 'users' table.

MySQL

DROP INDEX [1] ON users;

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

Aemail_idx

Busers_email_idx

Cidx_email

Dindex_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using the column name instead of the index name

Confusing table name with index name

The DROP INDEX statement requires the exact index name. Here, it is idx_email.

3fill in blank

hard

Fix the error in the code to add a unique index on the 'username' column in the 'accounts' table.

MySQL

CREATE [1] INDEX idx_username ON accounts (username);

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

APRIMARY

BFULLTEXT

CFOREIGN

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY instead of UNIQUE

Using FOREIGN which is for foreign keys

Using FULLTEXT which is for text searching

To enforce uniqueness, use UNIQUE before INDEX. Other options are for different purposes.

4fill in blank

hard

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

MySQL

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

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

AINDEX

BUNIQUE

Cidx_name

Didx_fullname

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE when not needed

Using a name that doesn't reflect the columns

The statement creates a normal index named idx_fullname on two columns.

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]);

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

AFULLTEXT

Bidx_content

Ctitle, body

Dcontent_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using normal INDEX instead of FULLTEXT

Incorrect column list format

Using wrong index name

This creates a fulltext index named idx_content on the columns title and body.