Indexes & Performance · SQL SQL
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 WHERE clause?
Practice question from Indexes & Performance (SQL SQL), with the correct answer and a step-by-step solution.
medium · MCQ · Q-A1B2F6
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 WHERE clause?
- A.Using an equality operator like WHERE id = 100
- B.Applying a deterministic function or expression directly to the indexed column, such as WHERE UPPER(email) = '[email protected]'Correct answer
- C.Sorting the results using ORDER BY on the leading column of the index
- D.Performing an inner join on an indexed foreign key column
Solution
Wrapping an indexed column in a function like UPPER() alters the stored values, making standard B-tree indexes unusable unless a functional/expression index has been explicitly created.
More from Indexes & Performance
- What is the primary purpose of a B-tree index on a relational database column?
- Consider a table users with columns (id SERIAL PRIMARY KEY, email VARCHAR(255), status VARCHAR(50)). If a query frequently filters by email…
- What does a 'covering index' achieve in relational database performance optimization?
- 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