Which technique helps optimize SQL queries?

• A. Avoid using indexes to simplify queries.
• B. Always use subqueries instead of joins.
• C. Use EXPLAIN PLAN to inspect query plans, create appropriate indexes, rewrite queries for set-based operations.
• D. Use N+1 queries to fetch rows one by one.

Answer: (C)

Understanding how a SQL query will be executed is essential for optimization. Using EXPLAIN PLAN lets you see the exact steps the database will take to run the query—what indexes are used, how joins are performed, and whether a table is scanned entirely. This visibility lets you target performance improvements where it matters.

From the plan, you can create or adjust indexes on columns used in predicates and joins to speed up data access. You can also rewrite queries to operate in set-based fashion, allowing the database to process many rows at once rather than handling rows one by one. This reduces unnecessary work and round trips, significantly boosting performance.

Avoiding indexes or relying on subqueries or per-row fetching tends to increase work for the database and slow things down. The described approach combines seeing how a query runs, adding appropriate indexing, and rewriting for set-based processing, which generally yields the best optimization results.