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.

Practice

1. Why do indexes speed up queries in MySQL?

easy

A. They store all data in memory for faster access.

B. They create shortcuts to quickly find data without scanning the whole table.

C. They compress the data to reduce size.

D. They automatically backup the database.

Solution

1. Step 1: Understand what an index does

   An index works like a shortcut or a map to find data quickly instead of checking every row.

2. Step 2: Compare with full table scan

   Without an index, MySQL must look at every row, which is slow for big tables.

3. Final Answer:

   They create shortcuts to quickly find data without scanning the whole table. -> Option B

4. Quick Check:

   Indexes = shortcuts to data [OK]

Hint: Think of index as a book's index for fast lookup [OK]

Common Mistakes:

• Confusing indexes with data compression
• Thinking indexes store all data in memory
• Believing indexes backup data automatically

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 ON users email_index(email);

B. INDEX CREATE email_index ON users(email);

C. CREATE users INDEX email_index(email);

D. CREATE INDEX email_index ON users(email);

Solution

1. Step 1: Recall the 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.

3. Final Answer:

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

4. Quick Check:

   CREATE INDEX index_name ON table(column) [OK]

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

Common Mistakes:

• Swapping keywords order
• Missing ON keyword
• Placing index name after table name incorrectly

3. Given the table products(id INT, name VARCHAR(50), price DECIMAL) with an index on price, what will be the effect of this query?
EXPLAIN SELECT * FROM products WHERE price = 100;

medium

A. The query will use the index on price to find matching rows quickly.

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

C. The query will cause a syntax error due to missing index hint.

D. The query will lock the entire table during execution.

Solution

1. Step 1: Understand EXPLAIN output with index

   When a column has an index and the query filters on that column, MySQL uses the index to speed up search.

2. Step 2: Analyze query behavior

   The query filters by price = 100, so the index on price helps find rows without scanning all.

3. Final Answer:

   The query will use the index on price to find matching rows quickly. -> Option A

4. Quick Check:

   Index on filtered column = faster search [OK]

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

Common Mistakes:

• Assuming index is ignored without hints
• Thinking syntax error occurs without hints
• Believing table locks always happen on SELECT

4. You created an index on column username but your query SELECT * FROM users WHERE username = 'alice'; is still slow. What could be the problem?

medium

A. The column username is not used in the WHERE clause.

B. The query syntax is incorrect and causes slow execution.

C. The index was not created properly or is corrupted.

D. Indexes slow down all queries by default.

Solution

1. Step 1: Check if index exists and is valid

   If the index was not created correctly or got corrupted, MySQL won't use it, causing slow queries.

2. Step 2: Verify query and column usage

   The query syntax is correct and uses the indexed column, so syntax or column usage is not the issue.

3. Final Answer:

   The index was not created properly or is corrupted. -> Option C

4. Quick Check:

   Index missing or broken = slow query [OK]

Hint: Check index existence if query is slow on indexed column [OK]

Common Mistakes:

• Blaming query syntax when it's correct
• Thinking indexes always slow queries
• Ignoring that column is used in WHERE clause

5. You have a large table orders with columns order_id, customer_id, and order_date. You often run this query:
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
Which indexing strategy will speed up this query best?

hard

A. Create a composite index on (customer_id, order_date).

B. Create separate indexes on customer_id and order_date.

C. Create an index only on order_date.

D. No index is needed; full table scan is faster.

Solution

1. Step 1: Understand composite index benefits

   A composite index on (customer_id, order_date) helps MySQL filter rows by both columns efficiently in one index.

2. Step 2: Compare with separate indexes

   Separate indexes may be used but are less efficient than a composite index for combined conditions.

3. Final Answer:

   Create a composite index on (customer_id, order_date). -> Option A

4. Quick Check:

   Composite index matches multi-column WHERE best [OK]

Hint: Use composite index for multi-column WHERE filters [OK]

Common Mistakes:

• Creating only single-column indexes for multi-column queries
• Assuming full table scan is faster on large tables
• Ignoring order of columns in composite index