SQL Database Indexing Quiz

A team notices queries scanning the entire customer table to find matches by email. They want faster lookups for this field. What general concept helps speed up such searches?

A store frequently filters products by category_id. Which indexing strategy supports faster WHERE category_id = ... lookups?

A company searches for customers by last_name and first_name together. Which concept supports using both fields for efficient lookups?

A query planner avoids using an index even though one exists. The filter compares an unindexed expression like LOWER(name). What index type could be used?

An analyst wants to prevent duplicate emails across users. Which index type ensures uniqueness while speeding lookups?

A team has a large table where only active rows are queried frequently. What index design helps skip inactive rows efficiently?

A developer wonders why indexing a low-selectivity column like 'is_deleted' offers little benefit. What concept explains this?

A search filters by state, but the planner prefers scanning the table. Only a few states exist. What explains the index not being used?

A designer notes that ordering results by created_at is slow. What indexing approach supports efficient ORDER BY created_at?

A query filtering by (city, last_name) regularly appears in logs. Which index pattern matches this usage?

A company tracks products by SKU. Lookups by SKU return a single row. Why is an index especially effective here?

A designer wants faster join operations on user_id between orders and customers. Why index user_id in both tables?

A query applies a filter on price but wraps it in a function like ROUND(price). Why does the planner skip the index?

A warehouse uses an index to ensure product codes remain unique. Beyond validation, what performance benefit does this provide?

A developer asks whether indexing every column improves overall performance. What core concept disproves this?

A planner chooses an index scan instead of a table scan because only a small percentage of rows match a condition. What factor influenced this choice?

A new analyst learns that indexes are stored in tree-like structures. What advantage does this provide for lookups?

A query applies ORDER BY last_name. Why does an index on last_name help with sorting?

A team filters by (region, created_at). Queries rarely filter by region alone. Which composite order is most beneficial?

A developer designs a partial index for active users. What is the main benefit of partial indexing?

A query planner chooses a table scan despite an index because the filter matches most rows. What concept applies?

A system uses an index on email but adds a filter using CONCAT. Why might the index not be used?

A developer wonders why indexes slow down inserts slightly. What structural cost explains this?

An application sorts search results by score. A designer wants to reduce the sorting cost. What supports this?

A developer asks why indexing a frequently updated column might be avoided. What design trade-off is involved?

A composite index (x, y) is created, but queries filter only by y. Why is the index largely ineffective?

A planning tool reports a bitmap scan combining multiple indexes for complex filtering. Why might multiple indexes be used?

A designer sees that table scans outperform index scans for tiny tables. Why is this expected?

This index is added to speed lookups by email. What type of queries benefit most?

CREATE INDEX idx_users_email ON users(email);

What type of index does this create?

CREATE UNIQUE INDEX idx_users_username ON users(username);

This example creates a composite index. What filter pattern benefits most?

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Why might this functional index be helpful?

CREATE INDEX idx_lower_name ON users(LOWER(name));

What does this partial index optimize?

CREATE INDEX idx_active_users ON users(id) WHERE active = true;

This structure supports ordering. Which queries benefit?

CREATE INDEX idx_items_created_at ON items(created_at);

This composite index is created, but what usage pattern does it help?

CREATE INDEX idx_logs_level_time ON logs(level, created_at);