There are two tables:

general table

Does it make sense to break it into two:

two

To get an increase in the speed of a query when accessing its individual fields (name or data).

  • And due to what are you going to get a speed increase with this separation? Yes, of course more records will fit into the data block, and a full table scan to get one field will be faster. And brute force in any case should be avoided. Selection of one field from one record by id will be the same. But getting two fields at the same time (a very frequent task) or creating a new record - many times slower - Mike
  • @Mike, and I think so, just the lead programmer proves the opposite to me that even if we work with one field there will be a complete search through all the fields. (2) If, while writing to the second field, data gets NULL (the default value), the speed will remain the same as when writing one field. Just checking that I'm not a fool) - perfect
  • Did the lead programmer have a last name Susanin? He leads the wrong way :) - Mike
  • Of course, enumeration is present when the record length is not fixed, but they are in RAM and this is simply considered decoding the record. You may lose a couple of milliseconds on a million records due to jumping over the field to get to the other. But the most expensive operation in the database is disk access. And there are several records in the data block, and in each record all its fields. And the block is always read from the disk and all the fields after that are already on hand - Mike
  • @Mike well, if we look objectively, we also lose time on the condition that we combine two tables into one with a full sample. so I don’t see any gain. But as for Susanin, there’s no way to get away from him, I’ll do as he wants, and I’ll keep the correct scheme in my head) - perfect

1 answer 1

The priority goal of normalization is to eliminate data redundancy. Of course, not to the detriment of the performance drop. In this example, most likely, the speed will not increase. Because the total number of indices across the fields will remain the same.

In any case, it is desirable to make a couple of "non-synthetic" requests and analyze their execution plan. It will be more accurate.