[Solved] You want to optimize this query:SELECT user_id, last_login, status FROM users WHERE status = 'active' AND last_login > '2024-01-01';Which covering index will best speed up this query? | SQL

SQL - Indexes and Query Performance

You want to optimize this query:

SELECT user_id, last_login, status FROM users WHERE status = 'active' AND last_login > '2024-01-01';

Which covering index will best speed up this query?

ACREATE INDEX idx_users_userid ON users(user_id);

BCREATE INDEX idx_users_login_status ON users(last_login, status);

CCREATE INDEX idx_users_status_login ON users(status, last_login, user_id);

DCREATE INDEX idx_users_status ON users(status);

Step-by-Step Solution

Solution:

Step 1: Identify columns used in WHERE and SELECT
The query filters by status and last_login, and selects user_id, last_login, and status.
Step 2: Choose index covering all these columns
CREATE INDEX idx_users_status_login ON users(status, last_login, user_id); includes status, last_login, and user_id, covering all needed columns for filtering and selection.
Step 3: Confirm column order for filtering
Status is first in the index, matching the equality filter, then last_login for range filter, optimizing index usage.
Final Answer:
CREATE INDEX idx_users_status_login ON users(status, last_login, user_id); -> Option C
Quick Check:
Covering index with WHERE and SELECT columns in filter order [OK]

Quick Trick: Include all WHERE and SELECT columns in index, filter columns first [OK]

Common Mistakes:

Ignoring SELECT columns in index
Wrong column order reducing index efficiency
Creating index on only one filter column

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You want to optimize this query:

Step 1: Identify columns used in WHERE and SELECT

Step 2: Choose index covering all these columns

Step 3: Confirm column order for filtering

Final Answer:

Quick Check:

Want More Practice?