Index maintenance in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Dropping an Index on Query Performance

Consider a MySQL table employees with a non-unique index on the last_name column. You run the following query:

SELECT * FROM employees WHERE last_name = 'Smith';

If you drop the index on last_name, what is the most likely effect on the query's execution?

MySQL

DROP INDEX idx_last_name ON employees;

A The query will run slower because it will perform a full table scan.

B The query will run faster because the index is removed.

C The query will return no results because the index is missing.

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

💡 Hint

Think about how indexes help MySQL find rows quickly.

Indexes help MySQL locate rows without scanning the entire table. Dropping the index forces MySQL to scan all rows, making the query slower.

🧠 Conceptual

intermediate

Purpose of Index Maintenance in Databases

Why is regular index maintenance important in a MySQL database?

A To automatically update data in the tables without queries.

B To prevent users from accessing the database during peak hours.

C To reduce disk space by deleting all indexes periodically.

D To ensure indexes remain efficient by reorganizing or rebuilding them.

💡 Hint

Think about what happens to indexes as data changes over time.

As data is inserted, updated, or deleted, indexes can become fragmented or less efficient. Maintenance like rebuilding or reorganizing keeps them fast.

📝 Syntax

advanced

Correct Syntax to Rebuild an Index in MySQL

Which of the following commands correctly rebuilds an index named idx_customer_name on the customers table?

A ALTER TABLE customers DROP INDEX idx_customer_name, ADD INDEX idx_customer_name (customer_name);

B REBUILD INDEX idx_customer_name ON customers;

C OPTIMIZE TABLE customers REBUILD INDEX idx_customer_name;

D ALTER INDEX idx_customer_name REBUILD ON customers;

💡 Hint

MySQL does not have a direct REBUILD INDEX command.

MySQL rebuilds an index by dropping and recreating it using ALTER TABLE commands. Options A, C, and D are invalid syntax.

❓ optimization

advanced

Choosing the Best Index Type for Frequent Range Queries

You have a large MySQL table with a date_of_purchase column. You often run queries filtering purchases between two dates. Which index type is best to optimize these range queries?

A A HASH index on <code>date_of_purchase</code>.

B A FULLTEXT index on <code>date_of_purchase</code>.

C A BTREE index on <code>date_of_purchase</code>.

D No index is needed for range queries.

💡 Hint

Consider which index type supports range scans efficiently.

BTREE indexes support range queries efficiently. HASH indexes are good for exact matches but not ranges. FULLTEXT is for text searching.

🔧 Debug

expert

Diagnosing Slow Query Despite Index Presence

A query filtering on email column is slow even though there is an index on email. Which reason below best explains this?

A The index is corrupted and needs to be rebuilt.

B The query uses a function on <code>email</code> column, preventing index use.

C The table is too small to benefit from indexes.

D The index is a FULLTEXT index, which is not used for filtering.

💡 Hint

Think about how functions in WHERE clauses affect index usage.

Using functions on indexed columns in WHERE clauses disables index usage because MySQL cannot use the index for transformed values.

Practice

1. What is the main purpose of maintaining indexes in a MySQL database?

easy

A. To speed up data searches and queries

B. To increase the size of the database

C. To delete all data from tables

D. To backup the database automatically

Solution

1. Step 1: Understand what indexes do

   Indexes help the database find data faster, like an index in a book.

2. Step 2: Identify the main benefit of index maintenance

   Maintaining indexes keeps searches quick and efficient.

3. Final Answer:

   To speed up data searches and queries -> Option A

4. Quick Check:

   Indexes improve search speed = D [OK]

Hint: Indexes speed up queries by organizing data [OK]

Common Mistakes:

• Thinking indexes increase database size only
• Confusing index maintenance with data backup
• Believing indexes delete data

2. Which of the following is the correct syntax to drop an index named idx_name from a table users in MySQL?

easy

A. DELETE INDEX idx_name FROM users;

B. DROP INDEX idx_name ON users;

C. REMOVE INDEX idx_name IN users;

D. DROP idx_name FROM users;

Solution

1. Step 1: Recall MySQL syntax for dropping an index

   The correct command is DROP INDEX index_name ON table_name;.

2. Step 2: Match the syntax with options

   DROP INDEX idx_name ON users; matches the correct syntax exactly.

3. Final Answer:

   DROP INDEX idx_name ON users; -> Option B

4. Quick Check:

   DROP INDEX ... ON ... is correct syntax = B [OK]

Hint: Use DROP INDEX index_name ON table_name; to remove indexes [OK]

Common Mistakes:

• Using DELETE or REMOVE instead of DROP
• Omitting ON keyword
• Wrong keyword order

3. Given the table products with an index on category_id, what will be the effect of running OPTIMIZE TABLE products;?

medium

A. It renames the products table

B. It deletes all rows from the products table

C. It adds a new index on category_id automatically

D. It rebuilds the table and its indexes to improve performance

Solution

1. Step 1: Understand what OPTIMIZE TABLE does

   OPTIMIZE TABLE rebuilds the table and its indexes to reduce fragmentation and improve speed.

2. Step 2: Analyze the options

   Only It rebuilds the table and its indexes to improve performance correctly describes this effect.

3. Final Answer:

   It rebuilds the table and its indexes to improve performance -> Option D

4. Quick Check:

   OPTIMIZE TABLE rebuilds table and indexes = A [OK]

Hint: OPTIMIZE TABLE rebuilds indexes and table for better speed [OK]

Common Mistakes:

• Thinking OPTIMIZE deletes data
• Assuming it adds indexes automatically
• Confusing it with renaming tables

4. You tried to drop an index using DROP INDEX idx_user ON users; but got an error. What is a likely cause?

medium

A. The table users does not exist

B. You must use DELETE INDEX instead of DROP INDEX

C. The index name idx_user does not exist on the table

D. You need to restart the MySQL server before dropping indexes

Solution

1. Step 1: Check common reasons for DROP INDEX errors

   An error usually occurs if the index name is wrong or missing.

2. Step 2: Evaluate the options

   The index name idx_user does not exist on the table is the most common cause; other options are incorrect commands or unnecessary actions.

3. Final Answer:

   The index name idx_user does not exist on the table -> Option C

4. Quick Check:

   Wrong index name causes DROP INDEX error = C [OK]

Hint: Verify index name exists before dropping it [OK]

Common Mistakes:

• Using DELETE instead of DROP
• Assuming server restart is needed
• Not checking table existence first

5. You have a large table orders with a composite index on (customer_id, order_date). After frequent inserts and deletes, queries on customer_id are slow. What is the best index maintenance step to improve performance?

hard

A. Run OPTIMIZE TABLE orders; to rebuild the table and indexes

B. Drop the composite index and create separate indexes on each column

C. Delete all rows and reload the table

D. Rename the table to refresh indexes

Solution

1. Step 1: Understand index fragmentation impact

   Frequent inserts and deletes cause index fragmentation, slowing queries.

2. Step 2: Choose the best maintenance action

   OPTIMIZE TABLE rebuilds indexes and reduces fragmentation, improving query speed.

3. Step 3: Evaluate other options

   Dropping and recreating indexes separately may not help and can be costly; deleting data or renaming table is unrelated.

4. Final Answer:

   Run OPTIMIZE TABLE orders; to rebuild the table and indexes -> Option A

5. Quick Check:

   OPTIMIZE TABLE fixes fragmentation = A [OK]

Hint: Use OPTIMIZE TABLE to fix fragmented indexes after many changes [OK]

Common Mistakes:

• Dropping composite index without reason
• Thinking renaming table refreshes indexes
• Deleting data instead of maintaining indexes