Why indexes speed up queries in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the 'name' column of the 'employees' table.

MySQL

CREATE INDEX [1] ON employees(name);

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

Aidx_name

Bname_index

Cindex_name

Demployees_name_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved words as index names.

Leaving the index name blank.

Using spaces or special characters in the index name.

The index name can be any valid identifier. Here, idx_name is a simple and clear name for the index on the 'name' column.

2fill in blank

medium

Complete the query to select all rows where the 'age' is greater than 30, using the index on 'age' to speed up the search.

MySQL

SELECT * FROM employees WHERE age [1] 30;

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

A=

B<

C>

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong comparison operator.

Using operators that do not match the query intent.

The query selects employees older than 30, so the operator should be >.

3fill in blank

hard

Fix the error in the query to use the index on 'department' to find employees in the 'Sales' department.

MySQL

SELECT * FROM employees WHERE department [1] 'Sales';

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

ALIKE

B=

CIN

DBETWEEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIKE without wildcards.

Using BETWEEN for a single value.

Using IN with a single string without parentheses.

To find exact matches in the 'department' column, use the = operator. This allows the index to be used efficiently.

4fill in blank

hard

Fill both blanks to create a query that uses an index to find employees with salary {{BLANK_1}} 50000 and order the results by salary {{BLANK_2}}.

MySQL

SELECT * FROM employees WHERE salary [1] 50000 ORDER BY salary [2];

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

A>

BASC

CDESC

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > for salary comparison.

Ordering ascending when descending is intended.

The query finds employees with salary greater than 50000 and orders them from highest to lowest salary, so the blanks are filled with > and DESC.

5fill in blank

hard

Fill all three blanks to create a query that uses an index to find employees with experience {{BLANK_1}} 5, selects their {{BLANK_2}}, and orders by {{BLANK_3}} ascending.

MySQL

SELECT [2] FROM employees WHERE experience [1] 5 ORDER BY [3] ASC;

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

A>=

Bname

Cexperience

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of >= for experience.

Selecting experience instead of name.

Ordering by name instead of experience.

The query selects employees with experience greater or equal to 5, selects their names, and orders by experience ascending. So the blanks are >=, name, and experience.