The usual bottleneck.
WHAT TO FIND FIRST
Slow queries, from the database's own reporting.
WHAT TO EXAMINE
The execution plan, showing how the database will satisfy the query.
WHAT TO LOOK FOR
Full table scans where an index should be used Sorting large result sets Joins producing far more rows than expected
WHAT INDEXES DO
Allow the database to locate rows without examining every one.
WHAT TO INDEX
Columns used in filtering, joining and sorting.
WHAT TO BE CAREFUL WITH
Too many indexes, slowing writes Indexes never used Index column order, which determines usefulness for a given query
WHAT TO SELECT
Only the columns needed.
WHY
Selecting everything transfers and parses data you discard.
WHAT TO AVOID
Queries inside loops Loading entire tables to count or filter in code Sorting in the application what the database could sort
WHAT TO USE FOR LARGE OPERATIONS
Batching, rather than one enormous statement.
WHAT TO TEST
Query behaviour at production data volume, not at development volume.
