Database design and optimisation

Most slow databases are missing the obvious index

When an application slows down as it grows, the cause is usually not the server or the database engine. It is a handful of queries scanning entire tables because no index matches how they search. A query that took 20 milliseconds with 1,000 rows can take seconds with a million, and nobody notices until customers do.

We start from evidence: the database's own statistics on which queries run most often and take longest. That usually points to a short list of fixes, such as an index on the columns a common filter uses, a query rewritten to avoid loading whole tables, or a missing link between tables that forces repeated lookups. We measure each query before and after, so the improvement is visible rather than claimed.

Constraints stop bad data at the door

Many databases rely on the application to keep data clean. The app checks that an email is unique, that an order belongs to a real customer, that a quantity is not negative. Then a second app, an import script or a hurried manual fix skips those checks, and duplicates and orphaned records creep in. Reports start disagreeing, and nobody is sure which number is right.

A well-designed schema enforces the important rules itself: unique constraints, foreign keys, check constraints and sensible defaults. Whatever writes to the database has to follow them. For an existing system, we find where data has already gone wrong, plan the clean-up and then add the constraints so it does not happen again.

• Unique constraints on things that must not repeat, such as emails or invoice numbers
• Foreign keys so records cannot point at something that does not exist
• Check constraints for values that must stay in range
• Indexes chosen from real query patterns, not added everywhere