Composite indexes in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a composite index on columns 'first_name' and 'last_name'.

MySQL

CREATE INDEX idx_name ON users([1]);

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

Afirst_name, last_name

Bfirst_name

Clast_name

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Creating an index on only one column instead of both.

Forgetting to separate columns with a comma.

The composite index includes both 'first_name' and 'last_name' columns to speed up queries filtering by both.

2fill in blank

medium

Complete the query to use the composite index on 'city' and 'state' for filtering.

MySQL

SELECT * FROM customers WHERE city = 'Boston' AND [1] = 'MA';

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

Azipcode

Bcountry

Cstate

Daddress

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column not part of the composite index.

Using only one column when the index covers two.

The composite index is on 'city' and 'state', so filtering by both uses the index efficiently.

3fill in blank

hard

Fix the error in the composite index creation statement.

MySQL

CREATE INDEX idx_location ON places([1]);

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

Acity state

Bcity; state

Cstate, city,

Dcity, state

Attempts:

3 left

💡 Hint

Common Mistakes

Separating columns with spaces instead of commas.

Adding extra commas or semicolons inside the parentheses.

Columns in an index must be separated by commas without extra characters.

4fill in blank

hard

Fill both blanks to create a composite index on 'email' and 'signup_date'.

MySQL

CREATE [1] idx_user ON users([2]);

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

AINDEX

BTABLE

Cemail, signup_date

Demail signup_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'TABLE' instead of 'INDEX'.

Separating columns with spaces instead of commas.

The correct syntax uses 'INDEX' keyword and columns separated by commas.

5fill in blank

hard

Fill all three blanks to select rows using a composite index on 'category' and 'price'.

MySQL

SELECT * FROM products WHERE [1] = 'Books' AND [2] [3] 20;

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

Acategory

Bprice

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using columns not in the composite index.

Using the wrong comparison operator.

The query filters by 'category' and 'price' using the composite index. The operator '<=' selects products priced 20 or less.