In this guide 6 sections
An index is a sorted lookup that lets MySQL jump to matching rows instead of reading the whole table. A report that filters on an indexed column returns quickly, but one that filters on an unindexed column makes MySQL scan every row and slows as the table grows.
What an index does to a query
Think of the index at the back of a printed manual: you look up a term, read the page number and turn straight there. Each table gets a sorted B-tree.
Without it, the server reads the invoices table from the first row to the last, which is a full table scan with no shortcut. Ten thousand rows feel instant. Four million rows in an invoice_lines table do not, and the wait grows with every month of trading at the counter until the screen times out.
That explains your puzzle. The customer ledger opens at once because it searches on customer_id, which has an index, while the monthly sales report crawls because nothing helps it find rows by date.
Why column order in a composite index matters
A composite index covers two or more columns in a fixed order, much like a phone book sorted by surname and then by first name. You can find every Walker quickly. You cannot find every Maria quickly.
MySQL applies the leftmost prefix rule, so an index on (branch_id, invoice_date) serves a search on branch and a search on branch plus date. It fails a search on date alone. Dates are sorted only inside each branch.
Put the column you always filter by exact value first, put the range column, usually a date, after it, and ask why the columns sit in that order.
- Equality filters such as branch_id = 4 go first
- Range filters such as invoice_date between two days go next
- Sort columns come last when the report orders its rows
- A single column index on date will not rescue a branch filter
Why a date plus branch filter needs its own index
Most management reports ask one question: what did the Leeds branch sell between the first and the last day of the month? That is two conditions at once. Two single column indexes rarely cope.
MySQL usually picks one index and then checks the other condition row by row, which wastes time on rows that never matter. If it picks branch, it reads every invoice the branch ever raised, but a composite index on branch and date reads only the rows in the month.
Build indexes from the reports people actually run: list the five slowest screens, write down their filters and sort order, and design one index per pattern. Guesswork wastes whole days.
What indexes cost when staff save data
Every index is another sorted structure that MySQL must update on each insert, edit and delete, so billing counters posting hundreds of invoice lines an hour feel it. Reads get faster in return.
Indexes also take disk space, and a table with fifteen overlapping indexes writes slower than it should while filling the InnoDB buffer pool with structures nobody reads.
So add indexes with a reason. Drop duplicates, such as an index on branch_id alone when a composite on branch_id and invoice_date already exists in the same reporting table. Agree with your developer which tables are read heavy, like invoice_lines for reports, and which are write heavy, like the billing counter.
- Insert and update speed drops a little with each added index
- Disk and memory use rise with each added index
- Unused indexes are found by reviewing slow query logs
- Reporting tables can carry more indexes than billing tables
How EXPLAIN shows a full table scan
Your developer can put the word EXPLAIN in front of any query, and MySQL then describes its plan without running the query. Watch type, key and rows.
A type of ALL means a full table scan, a key of NULL means no index was used, and a rows estimate near the table size confirms it. After a good index is added, type changes to ref or range, and the rows estimate falls to the handful of rows the report truly needs.
Ask for a before and after EXPLAIN on every report your team calls slow, and attach both outputs to the ticket so the next developer sees what changed.
Slow report index worksheet
Each slow report gets a line. Columns hold report name, table, filter columns in order, sort column, rows in the table, EXPLAIN type before and after, index added, and the write cost the developer noted.
Open a blank worksheet to printQuestions owners ask
Why is my report slow when the table is small?
Small tables hide the problem because a scan of a few thousand rows finishes in a blink, but the same query on an older table scans millions of rows. Slowness often appears long after launch.
Can we simply add an index on every column?
No, because each index slows inserts and edits and uses memory, and MySQL will never choose many of them for any real query. Design from real report filters.
Does a new index change what the report shows?
No. An index changes how MySQL finds rows, not which rows match, so the numbers on the report stay identical while time taken and server load drop.
Who should check this, the developer or the owner?
The developer runs EXPLAIN and builds the index, while the owner supplies the report list and says which ones matter on busy mornings. Together they find the fix fast.