Creating indexes in MySQL - Quick Revision & Summary

Recall & Review

beginner

What is an index in a database?

An index is like a shortcut in a book. It helps the database find data faster without looking at every row.

beginner

How do you create a simple index on a column named 'name' in MySQL?

Use the command: CREATE INDEX idx_name ON table_name(name); This makes searching by 'name' faster.

intermediate

What is the difference between a UNIQUE index and a regular index?

A UNIQUE index makes sure no two rows have the same value in the indexed column, while a regular index just speeds up searches.

intermediate

Why should you not create too many indexes on a table?

Too many indexes slow down data changes like INSERT, UPDATE, DELETE because the database must update all indexes too.

intermediate

What is a composite index?

A composite index is an index on multiple columns together. It helps speed up searches that use those columns combined.

Which command creates an index on the 'email' column in MySQL?

A CREATE INDEX idx_email ON users(email);

B CREATE TABLE idx_email ON users(email);

C INSERT INDEX idx_email ON users(email);

D ALTER TABLE users ADD COLUMN idx_email(email);

The correct syntax to create an index is CREATE INDEX index_name ON table_name(column_name);

What does a UNIQUE index ensure?

A No duplicate values in the indexed column

B Faster data insertion

C Allows NULL values only

D Deletes duplicate rows automatically

A UNIQUE index prevents duplicate values in the indexed column.

Which is a downside of having many indexes on a table?

A Indexes automatically fix data errors

B Slower data retrieval

C More disk space saved

D Slower data modification operations

More indexes mean the database must update them on data changes, slowing down INSERT, UPDATE, DELETE.

What is a composite index?

A An index on a single column

B An index on multiple columns together

C An index that stores data in compressed form

D An index that automatically updates

A composite index covers multiple columns combined for faster searches using those columns.

Which of these is NOT a valid reason to create an index?

A Speed up search queries

B Enforce uniqueness of data

C Make data insertion faster

D Improve join performance

Indexes slow down data insertion because they need updating; they do not make it faster.

Explain what an index is and why it is useful in a database.

Think about how you find a topic quickly in a book.

Describe how to create a simple index in MySQL and mention one important consideration when using indexes.

Remember the command and why too many indexes can be bad.