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.

Practice

1. What is the main purpose of using an index in a MySQL database?

easy

A. To increase the size of the database

B. To speed up data retrieval by creating shortcuts

C. To backup the database automatically

D. To store data in a compressed format

Solution

1. Step 1: Understand what an index does

   An index creates a shortcut to quickly find data without scanning the whole table.

2. Step 2: Compare options with index purpose

   Only To speed up data retrieval by creating shortcuts describes speeding up data retrieval, which is the main use of indexes.

3. Final Answer:

   To speed up data retrieval by creating shortcuts -> Option B

4. Quick Check:

   Index = Speed up search [OK]

Hint: Indexes speed up searches by creating shortcuts [OK]

Common Mistakes:

• Thinking indexes compress data
• Confusing indexes with backups
• Believing indexes increase database size

2. Which of the following is the correct syntax to create an index on the column email in a table users?

easy

A. CREATE INDEX email_index ON users (email);

B. CREATE INDEX ON users email_index (email);

C. CREATE INDEX users.email_index (email);

D. INDEX CREATE email_index ON users (email);

Solution

1. Step 1: Recall correct CREATE INDEX syntax

   The correct syntax is: CREATE INDEX index_name ON table_name (column_name);

2. Step 2: Match syntax with options

   CREATE INDEX email_index ON users (email); matches the correct syntax exactly, others have wrong order or keywords.

3. Final Answer:

   CREATE INDEX email_index ON users (email); -> Option A

4. Quick Check:

   CREATE INDEX index_name ON table (column) [OK]

Hint: Remember: CREATE INDEX name ON table (column) [OK]

Common Mistakes:

• Swapping index name and table name
• Omitting ON keyword
• Wrong keyword order

3. Given a table orders with an index on customer_id, what will happen when you run this query?

SELECT * FROM orders WHERE customer_id = 123;

medium

A. The query will fail with a syntax error

B. The query will scan the entire table ignoring the index

C. The query will use the index to quickly find matching rows

D. The query will update the customer_id values

Solution

1. Step 1: Understand index usage in WHERE clause

   When a column in WHERE has an index, MySQL uses it to find rows faster.

2. Step 2: Analyze query behavior

   The query filters by customer_id, which has an index, so it uses the index to speed up search.

3. Final Answer:

   The query will use the index to quickly find matching rows -> Option C

4. Quick Check:

   Indexed column in WHERE = index used [OK]

Hint: WHERE on indexed column uses index automatically [OK]

Common Mistakes:

• Assuming full table scan always happens
• Confusing SELECT with UPDATE
• Expecting syntax errors in correct queries

4. You created an index on last_name but your query SELECT * FROM employees WHERE first_name = 'John'; is slow. What is the likely problem?

medium

A. The index on last_name is not used because the query filters on first_name

B. The index on last_name causes a syntax error

C. The query is missing a JOIN clause

D. The index automatically updates first_name values

Solution

1. Step 1: Identify which column is indexed

   The index is on last_name, but the query filters on first_name.

2. Step 2: Understand index usage

   Indexes only help if the filtered column is indexed; here first_name is not indexed, so index is unused.

3. Final Answer:

   The index on last_name is not used because the query filters on first_name -> Option A

4. Quick Check:

   Index column ≠ filtered column = no index use [OK]

Hint: Index helps only if filtered column matches indexed column [OK]

Common Mistakes:

• Expecting index on different column to help
• Thinking index causes syntax errors
• Confusing filtering with updating

5. You have a table products with columns category, price, and stock. You often run this query:

SELECT * FROM products WHERE category = 'Books' AND price < 20;

Which index strategy is best to speed up this query?

hard

A. Create an index only on price

B. Create separate indexes on category and stock

C. Create a composite index on (stock, price)

D. Create a composite index on (category, price)

Solution

1. Step 1: Analyze query filter columns

   The query filters on category and price together.

2. Step 2: Choose index covering both columns

   A composite index on (category, price) helps MySQL filter efficiently on both conditions.

3. Step 3: Evaluate other options

   Separate indexes or indexes on stock do not help this query's WHERE clause.

4. Final Answer:

   Create a composite index on (category, price) -> Option D

5. Quick Check:

   Composite index on filtered columns = best speed [OK]

Hint: Composite index on all filtered columns speeds queries best [OK]

Common Mistakes:

• Indexing unrelated columns
• Using separate indexes instead of composite
• Ignoring query filter columns