Is it possible for a hypothetic relational database, let's pretend its design shouldn't be semantic or entity-oriented, may result in select query working faster on joined tables than on a single table which holds all the information? I mean, should a database architect always stick to as few tables as possible and as many single tables as possible for select queries to be executed on them?
Asked
Active
Viewed 23 times
1 Answers
1
First of all, no matter what theoretical perfomance might be for hypothetical RDBMDs, a database architect shouldn't "stick to as few" nor "as many single..". Stick to good analysis and good design, not some superficial "maximizations". The standard database normalization levels are a very good guide, but not even them should be above specific analysis and measurement.
Said that, it's very easy to come up with a scenario where a join is much faster than a single table with 'optional' fields: any low-discrimination auxiliary table could be loaded in RAM and not reading those fields again more than compensates for reading less fields on the 'main' table.
For example, lets say you have a 'personal' table, with fields like first/last name, birth date, etc. and the sex field could be a foreign key to a table with the available options. Even Facebook has only a limited number of options there, so it would be quickly loaded in RAM and 'filled' from there. That would be much faster than reading a text field with the option description on each record.
Javier
- 60,510
- 8
- 78
- 126