5 Simple SQL Query Optimization Techniques for Production

Writing a query that works is easy. Writing a query that runs fast on millions of rows requires optimization. Here are 5 quick tips for production databases.

1. Stop using SELECT *

Always specify the exact columns you need. SELECT * forces the database to read unnecessary fields from disk, causing I/O congestion and rendering index-only scans impossible.

-- Use specific columns and prefix with EXPLAIN to inspect costs
EXPLAIN ANALYZE
SELECT name, salary FROM employees 
WHERE salary > 75000 AND department_id = 90;

2. Run EXPLAIN on slow queries

Prefix your queries with EXPLAIN to view the database query execution plan. Look for slow "Sequential Scans" and ensure the database is utilizing the indexes you defined.

3. Avoid wildcards at the beginning of LIKE

Queries like WHERE name LIKE '%john' cannot use a standard index, forcing a full scan. If possible, query using prefix matching: WHERE name LIKE 'john%'.