SQL Reference Guide

Revision Time: 2 mins

17. SQL Performance Tips Reference

Practical query optimization guidelines.

Avoid SELECT *

Return: Table

Always list target columns to prevent excessive network and disk reading overhead.

Syntax signature:SELECT col1, col2 FROM table;

Code snippet:

python

SELECT id, name FROM large_table;

Expected Output:Optimized memory scan.

Remember: Always ensure correct syntax formatting when calling Avoid SELECT *.

Filter early

Return: Value

Apply WHERE conditions aggressively before performing join queries to minimize Cartesian product sizes.

Syntax signature:Filter first, join after.

Code snippet:

python

SELECT * FROM (SELECT * FROM t1 WHERE val = 1) a JOIN t2 ON a.id = t2.id;

Expected Output:Reduced join table size.

Remember: Always ensure correct syntax formatting when calling Filter early.

Use indexes

Return: Value

Define index structures for columns queried inside WHERE, JOIN ON, and ORDER BY clauses.

Syntax signature:CREATE INDEX idx ON t(col);

Code snippet:

python

Enables fast search trees instead of slow linear scans.

Expected Output:Sub-millisecond index seek.

Remember: Always ensure correct syntax formatting when calling Use indexes.

Avoid unnecessary DISTINCT

Return: Value

Distinct invokes an expensive memory sorting operation; ensure duplicates are truly possible before calling.

Syntax signature:Drop DISTINCT if source table has primary key checks.

Code snippet:

python

SELECT email FROM users; -- users.email is already unique

Expected Output:Bypasses sort buffer requirements.

Remember: Always ensure correct syntax formatting when calling Avoid unnecessary DISTINCT.

Prefer EXISTS over IN

Return: Value

Exists terminates matching scans instantly on first match, unlike IN which builds entire arrays.

Syntax signature:WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id)

Code snippet:

python

SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

Expected Output:Fast lookup scans.

Remember: Always ensure correct syntax formatting when calling Prefer EXISTS over IN.

Limit returned columns

Return: Value

Restrict fields, especially when queries return large text objects or binary files.

Syntax signature:SELECT needed_columns FROM table;

Code snippet:

python

Avoid fetching large json/text column blobs.

Expected Output:Improved data transfer speeds.

Remember: Always ensure correct syntax formatting when calling Limit returned columns.

Previous Topic16. SQL Order of Execution Next Topic18. SQL One-Liners