The single biggest lever is indexing columns used in WHERE, JOIN, and ORDER BY, covered in the Indexing topic. Beyond that: select only the columns actually needed rather than SELECT *, avoid functions wrapped around indexed columns in WHERE (which can prevent the index from being used at all), be aware that correlated subqueries can be far slower than an equivalent JOIN, and use EXPLAIN to see what the query planner is actually doing rather than guessing.
Don't wrap an indexed column in a function
Applying a function to the indexed column itself often prevents the index from being used at all, since the engine would need to compute that function for every row to compare — defeating the entire point of the index.
-- BAD: LOWER(email) prevents using an index on email directly
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- BETTER: index the expression itself, or store a normalized column
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- now the same query above CAN use this indexPrefer JOIN over a correlated subquery where equivalent
Modern query planners often optimize these to the same plan, but a plain JOIN is both more readable and less likely to accidentally trigger the slow, per-row re-evaluation a correlated subquery can carry.
-- correlated subquery
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- often equivalent, and easier to reason about:
SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id;Select only what you need
SELECT * fetches every column even when only two are used downstream — wasted network transfer and memory on wide tables, and it can also prevent an "index-only scan" (where the engine satisfies a query entirely from the index, never touching the actual table) if the selected columns aren't all covered by the index.