Indexes & Performance · SQL SQL
Consider a table users with columns (id SERIAL PRIMARY KEY, email VARCHAR(255), status VARCHAR(50)). If a query frequently filters by email and statu…
Practice question from Indexes & Performance (SQL SQL), with the correct answer and a step-by-step solution.
medium · MCQ · Q-6B4B24
Consider a table users with columns (id SERIAL PRIMARY KEY, email VARCHAR(255), status VARCHAR(50)). If a query frequently filters by email and status using WHERE email = '[email protected]' AND status = 'active', which index design is most efficient?
- A.Two separate single-column indexes: CREATE INDEX idx_email ON users(email); CREATE INDEX idx_status ON users(status);
- B.A multi-column composite index: CREATE INDEX idx_email_status ON users(email, status);Correct answer
- C.A full-text search index on both columns
- D.No index is needed because equality filters use sequential scans faster
Solution
A composite index on (email, status) allows the database to navigate the B-tree using the leading column (email) and effectively filter the second column (status) within the same index structure. Separate single-column indexes force the database to use index intersection techniques, which are often much slower.
More from Indexes & Performance
- What is the primary purpose of a B-tree index on a relational database column?
- What does a 'covering index' achieve in relational database performance optimization?
- Which of the following scenarios is most likely to result in an index being ignored by the query optimizer during a SELECT query with a WHE…
- What is a partial index, and when is it most advantageous to use one?
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed