Indexing

A small Members table is easy to read from top to bottom. Six rows. Sophie is obvious.

A working library is not six rows. It may be thousands of members. If staff ask "who has 07700 900 123?", the software's basic method is to start at row 1 and keep going until it finds a match. That is a table scan: read every row, just in case.

You already know a better tool in ordinary life. A book's index does not ask you to read every page to find "irrigation". It keeps a sorted list of topics and the pages they appear on.

A database index is the same idea. The DBMS keeps a separate, ordered lookup for a column — Phone, for example — and uses it to jump to matching rows instead of walking the whole table.

The Members table stores names, cities, and phones. The index is another structure — maintained by the DBMS, not typed by staff.

It holds phone values in order, each pointing at the MemberID of the matching row. When staff search by phone, the software walks the index, not every full member record.

Primary keys are indexed automatically in the systems you will meet, including Microsoft Access. MemberID already has an index.

Add extra indexes on columns you search or sort often: Phone, Title, perhaps City if staff often filter by city.

Do not index every column. Each index is a second structure updated on every insert, change, or delete. Too many indexes make writing slower even as reading gets faster.