Index selection strategy in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

CREATE INDEX [1] ON users(email);

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

Ausers_email_idx

Bindex_email

Cemail_index

Didx_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using spaces or special characters in the index name.

Using reserved keywords as index names.

The index name idx_email is a common and clear naming convention for an index on the email column.

2fill in blank

medium

Complete the code to select all rows from orders where customer_id equals 5, using an index if available.

MySQL

SELECT * FROM orders WHERE [1] = 5;

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

Acustomer_id

Bstatus

Corder_id

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column not related to the filter condition.

Choosing a column that is not indexed.

The query filters by customer_id, so an index on this column helps speed up the search.

3fill in blank

hard

Fix the error in the index creation statement by completing the blank.

MySQL

CREATE INDEX [1] users(email);

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

AINDEX

BON

CFOR

DUSING

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the ON keyword causes syntax errors.

Using incorrect keywords like FOR or USING in this position.

The CREATE INDEX syntax requires the keyword ON before specifying the table name.

4fill in blank

hard

Fill both blanks to create a unique index named uniq_order on the order_number column of the orders table.

MySQL

CREATE [1] INDEX [2] ON orders(order_number);

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

AUNIQUE

BPRIMARY

Cuniq_order

Didx_order_number

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY instead of UNIQUE for a unique index.

Using a generic index name instead of the specified one.

The keyword UNIQUE creates a unique index, and uniq_order is the index name.

5fill in blank

hard

Fill all three blanks to write a query that selects product_id and price from products where category equals 'Books', using an index if available.

MySQL

SELECT [1], [2] FROM products WHERE [3] = 'Books';

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

Aproduct_id

Bprice

Ccategory

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not requested.

Filtering by a column other than category.

The query selects product_id and price columns and filters rows where category is 'Books'. An index on category helps speed up this filter.