Single column index in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a single column index in a database?

A single column index is a database structure that speeds up data retrieval by creating an index on just one column of a table.

beginner

How does a single column index improve query performance?

It allows the database to quickly locate rows based on the indexed column without scanning the entire table.

beginner

Which SQL statement creates a single column index on the column 'email' in the 'users' table?

CREATE INDEX idx_email ON users(email);

beginner

True or False: A single column index can be used to speed up queries filtering by that column only.

True. It is designed to optimize queries that filter or sort by the indexed column.

intermediate

What is a potential downside of creating too many single column indexes?

Too many indexes can slow down data insertion, updates, and deletions because the indexes need to be updated as well.

What does a single column index do?

A Speeds up queries filtering on one column

B Stores all table data in one column

C Deletes duplicate rows automatically

D Creates a backup of the database

A single column index helps the database find rows faster when filtering by that column.

Which SQL command creates a single column index on 'username' in table 'accounts'?

A DROP INDEX idx_username;

B CREATE TABLE idx_username (username);

C CREATE INDEX idx_username ON accounts(username);

D SELECT * FROM accounts WHERE username;

The CREATE INDEX statement creates an index on the specified column.

What happens if you create many single column indexes on a table?

A Table size decreases

B Inserts and updates become slower

C Queries become slower

D Database crashes

Indexes need updating on data changes, so many indexes slow down inserts and updates.

Can a single column index speed up queries filtering by multiple columns?

A No, it only helps with one column

B Yes, always

C Only if columns are combined in the index

D Only if the table is small

A single column index only helps queries filtering on that one column.

Which of these is a benefit of using a single column index?

A Compresses the table data

B Automatic data backup

C Prevents data duplication

D Faster data retrieval on that column

Indexes improve speed of data retrieval based on the indexed column.

Explain what a single column index is and how it helps database queries.

Think about how an index in a book helps you find a topic quickly.

Describe the tradeoffs of using single column indexes in a database.

Consider both benefits and costs of indexes.