4 ms·
While the normalized version is more compact and doesn't store redundant data, it _also_ needs an index on the four columns or it'll have to check every row in
by webstrand 5y ago
While the normalized version is more compact and doesn't store redundant data, it _also_ needs an index on the four columns or it'll have to check every row in the table. A similar index added to the original denormalized table would have given comparable query performance.
The table schema isn't terrible, it's just not great. A good first-pass to be optimized when it's discovered to be overly large.
- deleted 5y ago[deleted]
- deleted 5y ago[deleted]
- Closi 5y ago> The table schema isn't terrible, it's just not great. It depends - if this was the full use-case then maybe a single table is actually a pretty good solution.
- rk06 5y agothat is not true. the sub tables guarantee that value is unique in the entire column. while main table did not have any such guarantees, so, there will be a lot of indexing and duplication in atleast 3 (of 4 sub tables) Moreover, indexing would be much faster and efficient as there are only unique values in sub tables
- int_19h 5y agoWhy wouldn't it have such guarantees, if the indices in question are defined as UNIQUE?
- rk06 5y agoBecause they are not unique in main table. For eg: if there are multiple emails from same domain name, then domain name column will have same entry multiple times. When domain is moved to sub table, main table still has duplicate entries, in form of foreign keys, while domain table has unique entry for each domain name
- int_19h 5y agoForeign key columns (i.e. the ones in the main table) are often indexed themselves, and those indices can also be UNIQUE.