Tips 1–7: Fetching & Filtering

This is the start of the 30 SQL Performance Tips checklist. Every tip comes with a ❌ bad and ✅ good IPL example. But first, the golden rule that applies to all 30: always test using the execution plan — never guess. The same query can run 1000× faster after a small change, or not at all faster — only measurement tells you.

Always test: the 4-step loop

The 30 tips at a glance

Tip 1 — Select only what you need

Avoid SELECT * — fetch only the columns you really use.

-- ❌ Bad
SELECT * FROM players;

-- ✅ Good
SELECT name, runs FROM players;

Why: fewer columns = less data read, less memory, less network traffic. It also lets a narrow index cover the query (all needed columns are in the index, so no lookup to the table is needed). And your code won't break when someone adds a new column.

Tip 2 — Avoid DISTINCT and ORDER BY unless necessary

Use DISTINCT and ORDER BY only when absolutely necessary — both force a sort (or hash), which slows the query down.

-- ❌ Bad: de-duplicating and sorting "just in case"
SELECT DISTINCT * FROM players ORDER BY name;

-- ✅ Good: sort / de-duplicate only if the result really needs it
SELECT name, team FROM players;

The playground plan for SELECT DISTINCT team FROM players ORDER BY team shows an extra line USE TEMP B-TREE FOR DISTINCT — that's the cost you pay. DISTINCT on a primary-key column (or on SELECT * of a table with a PK) is pure waste: rows are already unique.

Tip 3 — Explore with TOP

Limit the rows while exploring a table, to avoid fetching data you don't need.

-- ❌ Bad: pulls millions of rows just to "have a look"
SELECT * FROM innings;

-- ✅ Good
SELECT TOP 10 * FROM innings;

Note: TOP without ORDER BY returns any 10 rows — fine for exploring, wrong for "top 10 scorers". MySQL/PostgreSQL/SQLite use LIMIT 10 at the end instead.

Tip 4 — Index your WHERE columns

Create non-clustered indexes on columns that are frequently used in WHERE.

-- ❌ Bad: WHERE team = 'MI' scans the whole table every time
SELECT name FROM players WHERE team = 'MI';

-- ✅ Good
CREATE INDEX IX_team ON players (team);

Tip 5 — No functions on columns in WHERE

Functions like UPPER(), YEAR(), LEFT(), ISNULL() wrapped around a column in WHERE block index usage.

-- ❌ Bad: the function must run on every row -> scan
SELECT name FROM player_profiles WHERE YEAR(debut) = 2008;

-- ✅ Good: compare the raw column with a range -> seek
SELECT name FROM player_profiles
WHERE debut >= '2008-01-01' AND debut < '2009-01-01';

Verified in the playground with an index on debut:

Both return the same 3 players (Dhoni, Kohli, Rohit). A condition that can use an index is called SARGable (Search ARGument-able): keep the column alone on one side of the operator.

Tip 6 — No leading wildcard

A LIKE pattern that starts with % cannot use an index (the B-Tree is sorted from the first character).

-- ❌ Bad: must check every name
SELECT name FROM players WHERE name LIKE '%Kohli';

-- ✅ Good: known prefix -> SQL Server can seek the name index
SELECT name FROM players WHERE name LIKE 'Virat%';

Think of a phone book: finding everyone whose name starts with "Vir" is easy; finding everyone whose name ends with "ohli" means reading the whole book. (For heavy "contains" searches, SQL Server offers full-text indexes.)

Tip 7 — IN instead of many ORs

Use IN instead of a long chain of OR conditions on the same column — better readability and performance.

-- ❌ Bad
SELECT name, team FROM players
WHERE team = 'MI' OR team = 'CSK' OR team = 'RCB';

-- ✅ Good
SELECT name, team FROM players
WHERE team IN ('MI', 'CSK', 'RCB');

Same result. The optimizer often treats both forms the same way, so IN is never worse — and it is much easier to read and maintain.

Common mistakes

• ❌ WHERE ISNULL(team, '') = 'MI' ✅ WHERE team = 'MI' (NULL never equals 'MI' anyway).
• ❌ WHERE runs + 100 > 600 ✅ WHERE runs > 500 — move the arithmetic to the constant side.
• ❌ SELECT TOP 3 name FROM players for "top 3 scorers" ✅ add ORDER BY runs DESC.

Interview tip

Q: What does "SARGable" mean? A: A predicate that can use an index seek — the indexed column appears alone, without a function or calculation around it, e.g. debut >= '2008-01-01' instead of YEAR(debut) = 2008, or LIKE 'Vir%' instead of LIKE '%ohli'.