An online retailer gathers transactions in a large table where the 'category' field is used frequently for filtering. The current queries take considerable time to return results. Which technique is most likely to provide a performance boost when filtering by 'category'?
Sort the category column automatically when a query is run
Add an index on the category column to limit the full table scans
Use a subquery filtering on the category column in each query
Placing an index on a frequently filtered column accelerates lookups and reduces the amount of data scanned. Subqueries do not necessarily speed up queries if they still require full scans. Sorting by default does not reduce search time unless data is already indexed in a helpful way. Partitioning by transaction ID focuses on a different column, which would not improve filtering by category.
Ask Bash
Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.
What is an index in a database, and how does it work?
Open an interactive chat with Bash
What are the drawbacks of using a subquery for filtering in SQL?
Open an interactive chat with Bash
What is table partitioning, and why might it not aid filtering by category?