The hitch with relational databases is that you need to know what you are doing. This is mostly true for programming in general and a big reason why a lot of software isn't much faster today than it was in 1995, even though processors are many orders of magnitude faster. It's much more visible with relational databases, as they tend to accumulate so much data that bad solutions immediately break.

In this case, I don't think the 3NF-solution they came up with was necessary, it typically ends up requiring really complicated transactions and in some cases it may even be slower because of cache thrashing. What they really needed was to index their table. You can index strings, that's fine. It's reasonably fast, like even with a hundred million rows it's doable.

"But why doesn't the database build indices automatically?", I hear someone about to type, it's because indices make inserts slower and require more memory. In many cases auto-indexing would probably help, but it would break a lot of use-cases too.

There are databases out there that analyse usage and filtering patterns and will generate and drop indexes automatically. That is a benefit to some people (normally more analytical workloads than critical production workloads for obvious reasons).

Most databases offer some sort of optimization tooling, not all of it automatic. You just gotta know it's there.

Thats just bad design, indexes would have solved that.

How? My understanding was that the four strings formed a natural key, and so wouldn't the index just be the size of the table? What do you gain from that? I'm a database noob so please enlighten me.

I thought the benefit here was that each of the four tables was much smaller (the original table was potentially the Cartesian join of all four mini tables) so fewer string comparisons were done.

I would have considered hashing the values in those four columns. Not sure how it would compare but if the string comparisons are the issue it might eliminate the problem without creating extra tables with indexes that need to be scanned. I wonder how that would compare.

In theory, you'd be performing fewer string comparisons. In practice, it's far from clear that the original schema was that bad, performance-wise (except for the nonsensical storage of IP addresses as strings). Even if you have separate indices for the columns, even just picking the index with the highest selectivity (which is something a good database engine will do naturally) will prune most of the comparisons in a logical conjunction that can't succeed. If you have a compound index, you can look for the exact values of multiple columns at once. And if the indices are implemented correctly in the database engine, they will be compressing the keys, so the index should be smaller than the table anyway.

> I would have considered hashing the values in those four columns.

That's a good idea that definitely works, but at least to me it's not clear that it's more efficient than a compound index or a computed index (where a compound index is basically the special case of a computed index on a concatenation that is supported natively even in RDBMS packages that don't support generalized computed indices). It might very well end up a wash.

I'm a bit rusty with MySQL, but it feels like he could have just indexed the original table...

That was a she, not a he.

> work so poorly

They don't work poorly, and there is no alternative. The database should not monitor the contents and opportunistically normalise the schema itself.

It's not "business rules", it's just normalisation.