Index selection strategy in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main purpose of using indexes in a database?

Indexes help the database find data faster, like a shortcut in a book's table of contents.

beginner

Which type of queries benefit most from indexes?

Queries that search for specific values or ranges, such as SELECT with WHERE clauses, benefit most from indexes.

intermediate

Why should you avoid indexing every column in a table?

Because indexes take extra space and slow down data changes like INSERT, UPDATE, and DELETE.

intermediate

What is a composite index?

An index that includes multiple columns to speed up queries filtering by those columns together.

advanced

How does MySQL choose which index to use for a query?

MySQL uses the query optimizer to pick the index that will return results fastest based on statistics and query conditions.

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

A Improving INSERT performance

B Helping with JOIN operations

C Speeding up SELECT queries

D Filtering rows faster in WHERE clauses

Indexes slow down INSERT operations because the index must be updated with new data.

What does a composite index contain?

A Multiple columns

B Only foreign keys

C Only primary keys

D One column only

A composite index includes multiple columns to optimize queries filtering by those columns together.

When should you consider adding an index to a column?

A When the column is rarely used in queries

B When the column stores large text blobs

C When the column is often used in WHERE clauses

D When the column is only used for sorting

Indexes help most when columns are frequently used to filter data in WHERE clauses.

What is a downside of having too many indexes?

A Queries run faster

B More disk space is used

C Data updates become slower

D Both B and C

Too many indexes use more disk space and slow down data changes like INSERT, UPDATE, and DELETE.

How does MySQL decide which index to use?

A Uses the first created index

B Uses the query optimizer based on statistics

C Randomly picks one

D Always uses the primary key index

MySQL's query optimizer chooses the best index based on data statistics and query conditions.

Explain how to decide which columns to index in a MySQL table.

Think about which columns help find data quickly and which slow down updates.

Describe the trade-offs involved in adding indexes to a database table.

Indexes are like shortcuts but cost extra to maintain.