Index maintenance in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main purpose of index maintenance in a database?

Index maintenance ensures that indexes remain efficient and up-to-date, which helps speed up data retrieval and keeps the database performing well.

beginner

What happens if indexes are not maintained regularly?

Indexes can become fragmented or outdated, causing slower queries and increased resource use because the database has to work harder to find data.

intermediate

Which MySQL command can be used to rebuild an index?

You can use ALTER TABLE table_name DROP INDEX index_name, ADD INDEX index_name (columns); or OPTIMIZE TABLE table_name; to rebuild and defragment indexes.

intermediate

What is index fragmentation and why is it important to fix it?

Fragmentation means the index pages are not stored in a continuous order, which slows down data access. Fixing it improves speed and reduces disk I/O.

intermediate

How does the OPTIMIZE TABLE command help with index maintenance in MySQL?

It reorganizes the physical storage of table data and associated indexes, reducing fragmentation and reclaiming unused space to improve query speed.

What is the effect of index fragmentation on database performance?

A No effect at all

B Slower query response times

C More efficient memory usage

D Faster data insertion

Fragmented indexes cause slower queries because the database reads scattered data pages.

Which MySQL command can help reduce index fragmentation?

A OPTIMIZE TABLE

B CREATE INDEX

C DROP TABLE

D SHOW INDEX

OPTIMIZE TABLE reorganizes data and indexes to reduce fragmentation.

Why should you maintain indexes regularly?

A To disable indexing

B To delete all data

C To increase disk space usage

D To keep queries fast and efficient

Regular maintenance keeps indexes efficient, improving query speed.

Which of these is NOT a method of index maintenance?

A Dropping unused indexes

B Defragmenting indexes

C Increasing table size

D Rebuilding indexes

Increasing table size is unrelated to index maintenance.

What does the ALTER TABLE command do in index maintenance?

A Rebuilds or modifies indexes

B Deletes the entire table

C Shows table structure

D Backs up the database

ALTER TABLE can drop and recreate indexes to rebuild them.

Explain why index maintenance is important for database performance.

Think about how data is stored and accessed.

Describe common methods to maintain indexes in MySQL.

Focus on commands and practices.