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
Indexing Strategies & Access Paths interview questions for placements and exams.
Questions
14
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.
Composite B-trees are left-prefix. Predicates on leading columns enable seeks. Skipping the first column usually prevents efficient use. Adding a selective range on created_at after equality filters still works well.
CREATE INDEX idx_loc_time ON places(country, city, created_at);
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.
When all needed columns are in the index (keys + included), the plan can avoid extra lookups. This cuts I/O and latency for hot paths. Use includes for non-key columns used in SELECT or WHERE to create index-only scans where supported.
-- SQL Server style CREATE INDEX ix ON orders(customer_id) INCLUDE (status,total); -- Postgres: just place columns in index and rely on index-only scan when visible
Create a composite index keyed by (user_id, created_at DESC, id DESC). The engine can seek on user_id and stream rows already in the required order. Adding id as a tiebreaker stabilizes order when timestamps collide and preserves index order across peers. Returning the first 50 rows becomes a cheap ordered index scan with an early stop.
CREATE INDEX idx_posts_feed ON posts(user_id, created_at DESC, id DESC);
Frequent random inserts or deletes fragment leaf pages, causing extra I/O. Page splits occur when an insertion lands in a full page and the engine splits it, which adds log volume and can cascade. Lowering fillfactor leaves free space to absorb new rows in hot ranges and reduces splits. Reorganize (or REINDEX concurrently) when fragmentation is moderate and uptime matters. Rebuild when fragmentation is severe, statistics are off, or you must change index definition or fillfactor. Schedule maintenance off-peak and monitor write pressure so benefit outweighs the cost.
-- SQL Server examples ALTER INDEX ix ON t REORGANIZE; ALTER INDEX ix ON t REBUILD WITH (FILLFACTOR = 90);
B-tree supports equality, range predicates, and ordered scans, which makes it the standard choice in most engines. Hash is limited to equality. Bitmap is great for low-cardinality analytics in certain systems. Full-text targets language search, not ordinary filters.
CREATE INDEX idx_users_created ON users(created_at);
A clustered index makes the table pages follow the index order, so range scans on that key are very efficient. There can be only one clustered index. It may slow random inserts into the middle of the key space.
-- SQL Server CREATE CLUSTERED INDEX cix_orders ON orders(order_date, id);
Some engines need to confirm row visibility (e.g., via visibility maps) before serving from the index alone. If visibility info is not set, they probe the base table. Regular vacuuming and recent access can improve index-only scans.
-- Postgres example: VACUUM ANALYZE helps visibility maps
Indexes shine when they can skip most rows. Low-selectivity predicates often end up scanning large portions of the table, reducing index value except in bitmaps or special cases.
CREATE INDEX idx_email ON users(email);
A partial index stores only rows that satisfy a predicate. It stays small and hot for common filters, reducing I/O and maintenance cost.
-- PostgreSQL CREATE INDEX idx_active ON users(email) WHERE active = true;
Bitmap indexes encode presence bits and combine quickly with AND/OR, ideal for star-schema filters on low-cardinality attributes. They are less suited to heavy OLTP writes due to maintenance overhead.
-- Engine-specific; common in Oracle and some warehouses
Every data change must keep indexes in sync. More indexes mean more writes, page splits, and log records. Index only where you gain more on reads than you lose on writes.
/* Review index usage and drop unused ones */
A leading wildcard prevents a seek. Use specialized text search: trigram or n-gram indexes (e.g., Postgres pg_trgm with GIN/GIST) or full-text search for token queries. For pure suffix matches, maintain a reverse-indexed column (store reversed strings) and seek on 'nos%'. If search can be constrained to prefix, change UX to anchor the left side (LIKE 'son%') which is index-friendly with B-tree.
-- Postgres trigram CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_trgm_name ON people USING GIN(name gin_trgm_ops); -- or store reversed_name and query WHERE reversed_name LIKE 'nos%';
SARGable predicates let the engine Search ARGuments directly via an index. Non-SARGable expressions wrap the column or start with a wildcard so the engine cannot seek. Rewrite DATE(created_at)=d as created_at >= d AND created_at < d+1. Rewrite LOWER(email)=x as email ILIKE x or use a functional index on LOWER(email). Avoid leading '%' in LIKE; prefer anchored prefixes or specialized text indexes.
/* Good date filter */ created_at >= '2025-10-01' AND created_at < '2025-10-02' /* Functional index option */ CREATE INDEX ix_email_lower ON users((lower(email)));
The optimizer estimates row counts from column and histogram statistics. If stats are stale or too generic, it may think many rows match and pick a scan. Update statistics, increase histogram buckets for skewed columns, or create filtered stats if supported. Also check parameter sniffing and ensure literals or sniffed values reflect common cases. Verify that predicates are SARGable.
-- Postgres ANALYZE users; -- SQL Server UPDATE STATISTICS users WITH FULLSCAN;