When indexes help and when they hurt in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the 'email' column of the 'users' table.

SQL

CREATE INDEX [1] ON users(email);

Drag options to blanks, or click blank then click option'

Aidx_email

Bemail_index

Cusers_email_idx

Dindex_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved keywords as index names.

Leaving out the index name entirely.

The index name should be unique and descriptive. 'users_email_idx' is a common naming convention.

2fill in blank

medium

Complete the code to select rows where 'last_name' is 'Smith' using the index.

SQL

SELECT * FROM employees WHERE last_name [1] 'Smith';

Drag options to blanks, or click blank then click option'

A!=

BLIKE

CIN

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' which excludes the value.

Using 'LIKE' without wildcards.

The '=' operator is used to find exact matches, which can use an index efficiently.

3fill in blank

hard

Fix the error in the query to use the index on 'created_at' for filtering recent records.

SQL

SELECT * FROM orders WHERE created_at [1] '2023-01-01';

Drag options to blanks, or click blank then click option'

A!=

B>=

C=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which limits to one date only.

Using '!=' which excludes the date.

Using '>=' selects records created on or after the date, which can use the index efficiently.

4fill in blank

hard

Fill both blanks to create a composite index on 'category' and 'price' columns.

SQL

CREATE INDEX [1] ON products([2], price);

Drag options to blanks, or click blank then click option'

Aidx_category_price

Bcategory

Ccategory_id

Didx_price_category

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names in the index.

Naming the index unclearly.

The index name 'idx_category_price' clearly describes the columns. The first column in the index is 'category'.

5fill in blank

hard

Fill all three blanks to write a query that uses an index to find products priced above 100 in category 'Electronics'.

SQL

SELECT * FROM products WHERE [1] = 'Electronics' AND [2] [3] 100;

Drag options to blanks, or click blank then click option'

Acategory

Bprice

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for price comparison.

Swapping column names in conditions.

The query filters by category 'Electronics' and price greater than 100, which can use an index on (category, price).

Practice

1. Which of the following best describes when an index helps in a database?

easy

A. When you want to reduce the size of the database file.

B. When you want to speed up inserting new rows.

C. When you want to quickly find rows based on column values.

D. When you want to delete all rows faster.

Solution

1. Step 1: Understand what indexes do

   Indexes help the database find rows faster by creating a quick lookup structure on columns.

2. Step 2: Identify when this is useful

   Searching rows by column values is faster with indexes, but inserts or deletes can slow down.

3. Final Answer:

   When you want to quickly find rows based on column values. -> Option C

4. Quick Check:

   Indexes speed up searching [OK]

Hint: Indexes speed up searches, not inserts or deletes [OK]

Common Mistakes:

• Thinking indexes speed up inserts
• Confusing index purpose with storage size
• Assuming indexes help deletes

2. Which SQL statement correctly creates an index on the column email in the table users?

easy

A. CREATE INDEX idx_email ON users(email);

B. CREATE users INDEX ON email;

C. INDEX CREATE ON users(email);

D. CREATE INDEX users_email email;

Solution

1. Step 1: Recall correct CREATE INDEX syntax

   The correct syntax is: CREATE INDEX index_name ON table_name(column_name);

2. Step 2: Match the options to syntax

   CREATE INDEX idx_email ON users(email); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_email ON users(email); -> Option A

4. Quick Check:

   Correct CREATE INDEX syntax = CREATE INDEX idx_email ON users(email); [OK]

Hint: CREATE INDEX index_name ON table(column) is correct syntax [OK]

Common Mistakes:

• Swapping table and column names
• Omitting ON keyword
• Wrong order of keywords

3. Given a table orders with 1 million rows and an index on customer_id, what is the expected effect of running SELECT * FROM orders WHERE customer_id = 123;?

medium

A. The query will use the index to quickly find matching rows.

B. The query will scan all rows, ignoring the index.

C. The query will be slower because indexes slow down SELECT queries.

D. The query will fail because indexes cannot be used in WHERE clauses.

Solution

1. Step 1: Understand index usage in WHERE clause

   Indexes help the database quickly locate rows matching the WHERE condition on indexed columns.

2. Step 2: Apply to the query

   Since customer_id is indexed, the query uses the index to find rows without scanning all 1 million rows.

3. Final Answer:

   The query will use the index to quickly find matching rows. -> Option A

4. Quick Check:

   Indexed WHERE columns speed up SELECT [OK]

Hint: WHERE on indexed columns uses index for fast lookup [OK]

Common Mistakes:

• Thinking indexes slow down SELECT
• Believing indexes are ignored in WHERE
• Assuming query fails with indexes

4. You notice that inserting rows into a table is very slow after adding several indexes. What is the most likely cause?

medium

A. Indexes cause syntax errors during inserts.

B. Indexes speed up inserts, so the problem is elsewhere.

C. The table is too small for indexes to matter.

D. Indexes slow down inserts because each insert updates all indexes.

Solution

1. Step 1: Understand index impact on inserts

   Each insert must update all indexes to keep them current, which adds overhead and slows inserts.

2. Step 2: Identify cause of slow inserts

   Adding many indexes increases this overhead, causing slower insert performance.

3. Final Answer:

   Indexes slow down inserts because each insert updates all indexes. -> Option D

4. Quick Check:

   More indexes = slower inserts [OK]

Hint: More indexes mean slower inserts due to update overhead [OK]

Common Mistakes:

• Thinking indexes speed up inserts
• Blaming syntax errors for slow inserts
• Ignoring index update cost

5. A table products has indexes on category_id and price. You run a query filtering on category_id and sorting by price. How can indexes help, and what might hurt performance?

hard

A. Indexes do not help filtering or sorting; they only help joins.

B. Index on category_id helps filtering; index on price helps sorting; but too many indexes slow inserts.

C. Indexes speed up inserts but slow down filtering and sorting.

D. Indexes on both columns cause syntax errors in queries.

Solution

1. Step 1: Analyze filtering and sorting with indexes

   The index on category_id speeds up filtering rows matching that category. The index on price can speed up sorting if used properly.

2. Step 2: Consider performance trade-offs

   Having multiple indexes helps reads but slows down inserts and updates because all indexes must be maintained.

3. Final Answer:

   Index on category_id helps filtering; index on price helps sorting; but too many indexes slow inserts. -> Option B

4. Quick Check:

   Indexes help filtering and sorting but slow inserts [OK]

Hint: Indexes help reads but slow writes when many exist [OK]

Common Mistakes:

• Believing indexes only help joins
• Thinking indexes speed up inserts
• Assuming indexes cause syntax errors