Explain index usage, sargability, and how to avoid SELECT * queries.

Index usage refers to the way a database engine uses an index to speed up data retrieval. When a query’s WHERE clause matches the indexed column(s), the engine can locate rows directly via the index rather than scanning the entire table, reducing I/O and CPU usage.

Sargability (Search Argument Able) means that a predicate can use an index. A predicate is sargable if it can be evaluated directly against the index key. For example, WHERE age = 30 is sargable, but WHERE YEAR(order_date) = 2020 is not, because the function prevents the engine from using an index on order_date. To keep predicates sargable, avoid wrapping indexed columns in functions, use range predicates that match the index order, and keep data types consistent.

Avoiding SELECT means explicitly listing only the columns you need. SELECT forces the database to read all columns, even those not used by the application, which can increase I/O, memory usage, and network traffic. By projecting only required columns, you reduce the amount of data the engine must fetch and send, which improves performance and can also allow the optimizer to choose a more efficient plan. In practice, write queries like SELECT id, name, email FROM users WHERE status = 'active' instead of SELECT * FROM users WHERE status = 'active'.