Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
SQL & Databases · Question Set
Indexes & Query Performance Essentials interview questions for placements and exams.
Questions
15
Included in this set
Subject
SQL & Databases
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for SQL & Databases.
A clustered index defines the physical order of rows on disk. The table and the index are the same structure, so range scans on the key are very fast. Because the table can be ordered only one way, you can have only one clustered index per table.
CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, -- often clustered by default order_date DATE, customer_id BIGINT );
For complete preparation, combine this set with full subject-wise practice for SQL & Databases. You can also explore other subjects and sets from the links below.
Start by capturing the plan to see join order, access paths, and estimated versus actual row counts. Look for scans on very large tables where a selective index could be used, and for big mismatches between estimated and actual rows that signal bad statistics or non-SARGable predicates. Then check operators with the highest cost or time. Add or adjust indexes to support the most selective predicates and common joins. Fix non-SARGable expressions, reduce columns (avoid select star), and ensure filters happen as early as possible. Re-run EXPLAIN ANALYZE to verify the change improved row counts and timing.
EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';
First, ensure foreign keys from the fact to each dimension are indexed. Add composite indexes that start with the join key and include common filters for selective access. Next, verify that the dimensions have primary or unique indexes on their keys for quick lookups. Run EXPLAIN to see join order and row estimates. Push selective filters into the earliest steps, and consider pre-aggregating the fact in a CTE or temp table if the report only needs rolled-up data. Validate statistics, and test alternative join orders by adding selective predicates or hints only as a last resort.
CREATE INDEX idx_fact_date ON fact_sales(date_id); CREATE INDEX idx_fact_customer ON fact_sales(customer_id); -- Verify unique indexes on dim_date(date_id), dim_customer(customer_id)
If the index contains the filter, join, and select columns, the engine can answer the query from the index alone. That avoids extra lookups and reduces I/O. In some engines you add non-key INCLUDE columns to achieve coverage without bloating the key.
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date) INCLUDE (total_amount);
A non-clustered index is a separate structure (key → row locator). It accelerates point lookups, filters, joins, and sorts. Choose columns that are selective and appear often in predicates or ordering to see the most benefit.
CREATE INDEX idx_users_email ON users(email);
High-selectivity columns filter out many rows per lookup. Unique keys are the most selective and typically index well. Low-cardinality columns like boolean flags rarely help alone, though they can help as the leading column in a composite index for special cases.
CREATE UNIQUE INDEX idx_users_email ON users(email);
Composite indexes are left-prefix. To fully seek, you must filter on the leading column(s). Filtering only on the second column often needs a scan. Plan index column order by your most common predicates and how they combine.
CREATE INDEX idx_users_cc_email ON users(country_code, email);
SARGable predicates let the engine search the index without transforming the column. Range predicates on the raw column preserve index use. Wrapping the column in a function forces a scan in many engines because the index on the base value cannot be used directly.
SELECT * FROM events WHERE created_at >= '2025-10-01' AND created_at < '2025-10-02';
A leading wildcard breaks the ability to seek from the left, so the index is not used. A prefix search like 'son%' can seek to the prefix range. If you need case-insensitive search, consider functional indexes or citext/GIN features where available.
SELECT * FROM people WHERE name LIKE 'son%';
When a parent row changes or is deleted, the engine must find matching children. An index on the child foreign key avoids full scans and reduces lock time. It also makes child-to-parent joins fast in day-to-day queries.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Statistics inform the optimizer about value distributions. If they are stale, row estimates drift and the plan can degrade. Refreshing stats helps the optimizer choose the right indexes and join strategies for current data.
-- engine specific -- PostgreSQL autovacuum/analyze or: ANALYZE orders; -- SQL Server: UPDATE STATISTICS orders;
OFFSET forces the engine to count and skip many rows, which grows linearly with page number. This wastes work and can lead to unstable pages as new rows arrive. Keyset pagination uses a stable, indexed cursor like an id or timestamp. You fetch the next page with a predicate on that key, which lets the engine seek directly to the next range. It is faster and consistent under concurrent inserts.
-- page 1 SELECT * FROM posts ORDER BY id ASC LIMIT 50; -- page 2 SELECT * FROM posts WHERE id > 50 ORDER BY id ASC LIMIT 50;
Create a filtered index when queries target a well-defined subset, like active=true, recent dates, or non-NULL values. The smaller index is cheaper to maintain and can be highly selective for those queries. The trade-off is coverage. Queries that fall outside the predicate cannot use the index. Be sure the predicate matches how the application filters in practice so the benefit is realized.
-- PostgreSQL CREATE INDEX idx_invoices_paid_recent ON invoices(paid_at) WHERE paid = true AND paid_at >= CURRENT_DATE - INTERVAL '90 days';
On every INSERT, UPDATE, or DELETE, each affected index must be updated. This adds CPU, I/O, and potential page splits. Over-indexed tables show slower bulk loads and heavier lock contention under write-heavy workloads. Mitigate by indexing only what you query often, and by consolidating composite indexes to cover multiple access patterns. For large one-time loads, disable or drop non-essential indexes and recreate them after the load. Batch writes and keep transactions short to reduce blocking.
/* Strategy example: create minimal indexes first, add others after profiling */
Create a functional index on the expression the query uses. Then queries that apply the same function can seek that index directly. This avoids scanning the whole table due to a function on the column. If your engine supports case-insensitive types or collations, consider them as an alternative so you can index the plain column and keep simpler predicates.
-- PostgreSQL CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- query SELECT * FROM users WHERE LOWER(email) = 'x@y.com';