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.