3 ms·

Indexes are basically a copy of the original data, only sorted in the right order. They are denormalized data, sort of.

by kolme 10y ago

hobs 10y ago
Indexes are often only a pointer to that data, and they are not denormalized, they are better described as projections of your data (this way saving the work of ordering/filtering on that same projection in the future.)
- throwanem 10y ago
  Not only that, they're projections which you don't have to spend effort on keeping up to date, and which the engine automatically uses to optimize your queries. While there's an argument to be made that the same can be accomplished in a non-relational database with suitable application code or a translation layer, all else equal it's better not to need those things in the first place.
  - phamilton 10y ago
    And as we saw from a recent Uber post about Postgresql, that automatic updating is not actually free.
    - hobs 10y ago
      Was going to say the same, but I realized the OP was talking about in your code, not so much as overhead.
    - throwanem 10y ago
      Of course it's not, but if you're not Uber, that almost certainly isn't a problem for you. When you use MongoDB, you get to roll your own indexes no matter who you are.
- kolme 10y ago
  Yes, they are pointers. But the indexed fields have to be duplicated for efficient reading. From Wikipedia: "An index is a copy of select columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from." [1] When you reference a field in an index, you're effectively duplicating this column on disk. We can discuss semantics, but at the end of the day, indexes are glorified copies of your columns in a specific order. [1] https://en.wikipedia.org/wiki/Database_index https://en.wikipedia.org/wiki/Database_index
tremon 10y ago
Are you suggesting that RAID-1 and RAID-5 are also examples of denormalized data, sort of?
- kolme 10y ago
  Not really, for two reasons: - It's done at the hard disk level, so no database or data schema is involved. - It's not done for performance reasons, but for security reasons (not to lose any data). "In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data." It's a technique that involves copying data for some very particular reason, an abstraction to hide the nasty duplication because the desirable thing is to have only one source of truth. So while there's some remote similarity (the copying of data), the goals are completely different.
  - jdmichal 10y ago
    A good RAID controller exactly fits your included quote. It will speed up reads by distributing to all disks, but writes will be slower because they have to be written to all disks before the controller will signal the write as completed.

Indexes are basically a copy of the original data, only sorted in the right order. They are denormalized data, sort of.

by kolme 10y ago

Indexes are basically a copy of the original data, only sorted in the right order. They are denormalized data, sort of.

hobs 10y ago

Indexes are often only a pointer to that data, and they are not denormalized, they are better described as projections of your data (this way saving the work of ordering/filtering on that same projection in the future.)

throwanem 10y ago
Not only that, they're projections which you don't have to spend effort on keeping up to date, and which the engine automatically uses to optimize your queries. While there's an argument to be made that the same can be accomplished in a non-relational database with suitable application code or a translation layer, all else equal it's better not to need those things in the first place.
- phamilton 10y ago
  And as we saw from a recent Uber post about Postgresql, that automatic updating is not actually free.
  - hobs 10y ago
    Was going to say the same, but I realized the OP was talking about in your code, not so much as overhead.
  - throwanem 10y ago
    Of course it's not, but if you're not Uber, that almost certainly isn't a problem for you. When you use MongoDB, you get to roll your own indexes no matter who you are.
kolme 10y ago
Yes, they are pointers. But the indexed fields have to be duplicated for efficient reading. From Wikipedia: "An index is a copy of select columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from." [1] When you reference a field in an index, you're effectively duplicating this column on disk. We can discuss semantics, but at the end of the day, indexes are glorified copies of your columns in a specific order. [1] https://en.wikipedia.org/wiki/Database_index https://en.wikipedia.org/wiki/Database_index

tremon 10y ago

Are you suggesting that RAID-1 and RAID-5 are also examples of denormalized data, sort of?

kolme 10y ago
Not really, for two reasons: - It's done at the hard disk level, so no database or data schema is involved. - It's not done for performance reasons, but for security reasons (not to lose any data). "In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data." It's a technique that involves copying data for some very particular reason, an abstraction to hide the nasty duplication because the desirable thing is to have only one source of truth. So while there's some remote similarity (the copying of data), the goals are completely different.
- jdmichal 10y ago
  A good RAID controller exactly fits your included quote. It will speed up reads by distributing to all disks, but writes will be slower because they have to be written to all disks before the controller will signal the write as completed.