3 ms·

Thats just bad design, indexes would have solved that.

• names_are_hard 5y ago
  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.
  • jhgb 5y ago
    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.