At a glance

An index is what lets a database find something without reading everything. Without the right ones, your application is fine at launch and unusable at scale, and nothing visible changes in between.

AI does not add them because it cannot know which ones you need. That is not a flaw that gets fixed by a better model. It is a consequence of what an index is.

What an index actually is

Think of a reference book with no index at the back.

Every fact in it is still there. You can absolutely find what you need. You just have to start at page one and read until you hit it, and if the book is nine hundred pages long, that is your afternoon gone.

The index at the back does not add information. It adds a shortcut, so you can jump to page four hundred and twelve directly.

A database index is exactly that. Without one, finding all orders for a particular customer means examining every order in the table, one at a time. With one, the database goes straight to them.

Why nothing is wrong at first

Because reading a short book cover to cover takes no time at all.

With two hundred orders, checking every one is imperceptible. It happens in a couple of milliseconds. Your application is fast, everything works, and you would never guess anything is missing.

At two hundred thousand orders, the same code doing the same thing is now reading two hundred thousand rows to find the eleven it wants. The code did not change. The data got bigger, and the shortcut that should have been there never was.

This is why it always shows up later, and why it correlates so neatly with success.

Why AI cannot get this right

An index is not a property of your data. It is a property of the questions you ask of your data, and AI has no visibility of those.

Three things determine which indexes you need, none of which are in the prompt:

  • What you search by, in practice. Not what the schema permits, but what your application and your staff actually look things up by, dozens of times a day.
  • How your data is distributed. An index on a column with two possible values is close to useless. On one with a hundred thousand distinct values it is transformative. The model cannot see your data.
  • Which operations you care about. Indexes make reads faster and writes slightly slower. Getting that trade-off right depends on your usage pattern, which is knowledge about your business, not about code.

Ask an AI for a table to store orders and it will produce a perfectly sensible one. It will not know that you look up orders by postcode every morning, because you never told it, and it did not think to ask.

What it feels like when it bites

Not a crash. That is what makes it insidious.

Things just get slower, gradually, over months, so nobody notices a single bad day. Staff start saying the system is a bit sluggish. Somebody blames the internet connection. A page that used to load instantly takes three seconds, then eight, and everyone adapts to it.

Then one day a report times out entirely, and it turns out the problem has been building for a year.

Along the way it quietly costs you: staff time, a worse experience for customers, and usually a bigger hosting bill, because the standard response is to pay for a more powerful server to compensate for a missing shortcut that would have been free.

The good news

Of everything that goes wrong in AI-built software, this is by far the cheapest to fix.

Finding out which indexes are missing is a matter of looking at what the application actually asks for. Adding them is usually a small, low-risk change. And unlike most performance work, the improvement is often enormous and immediate, which is a very satisfying way to spend an afternoon.

The reason to look is not that this is dangerous. It is that it is silently expensive, and the fix is trivial compared to the cost of tolerating it.

Indexes are one of the standard things we check in our AI Code Audit, a fixed-price £495 review that tells you plainly what you are dealing with. It is usually one of the first things we find.

Which is, once again, the theme of this series: AI is a brilliant typist and a terrible architect. It wrote a perfectly good query. Deciding how your data should be organised to answer it was never something it could do.

Noticed things getting slower and not sure why? Get in touch for a no-pressure conversation.